By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,790 Members | 1,409 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,790 IT Pros & Developers. It's quick & easy.

Union Queries & Filters

P: n/a
I have a union query joining two other queries, each with 62 fields.

Interestingly, when I open the query it has the correct number of 2850
records. But if I then set a filter (using filter by form), when I
apply the filter I get only 206 records, when I should get 208. If I
then remove the filter I get 2850 records again. But if I then click
on "apply filter" again I get only 186 records.

Each time I remove the filter I get the correct record count. But when
I reapply the filter I get different counts, never correct and never
the same.

But, if I apply the filter to the original two queries and then
perform the union, then I always get the correct count.

Can anyone explain this?
TIA,
Fred Zuckerman
San Diego, CA, USA
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
More information, please Fred.

Please give an example of the simplest filter that fails, e.g.:
ClientID = 27.
Since you are performing a UNION on other queries, please indicate if the
source fields of the filter are table fields, or calculated fields. If
tables, indicate the field types, e.g.:
tblClient1.ClientID = AutoNumber;
tblClient2.ClientID = Number (Long)
If calculated fields, indicate the calculations e.g.:
Query1.ClientID: 1 + 2
Query2.ClientID: 2 * 2

We may also need information on the queries: outer joins?

Any information you can glean on which fields are missing?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"Fred Zuckerman" <zu********@sbcglobal.net> wrote in message
news:2f**************************@posting.google.c om...
I have a union query joining two other queries, each with 62 fields.

Interestingly, when I open the query it has the correct number of 2850
records. But if I then set a filter (using filter by form), when I
apply the filter I get only 206 records, when I should get 208. If I
then remove the filter I get 2850 records again. But if I then click
on "apply filter" again I get only 186 records.

Each time I remove the filter I get the correct record count. But when
I reapply the filter I get different counts, never correct and never
the same.

But, if I apply the filter to the original two queries and then
perform the union, then I always get the correct count.

Can anyone explain this?
TIA,
Fred Zuckerman
San Diego, CA, USA

Nov 12 '05 #2

P: n/a
Here are the query definitions (they're long...)
I hope you have some suggestions...
TIA,
Fred
Definition For qryCombo
SELECT * FROM qryFull UNION SELECT * FROM qryTemp;

Definitions for qryFull
SELECT dbo_ViewRoster.*, tblRoster.SUPVID, tblRoster.OFCPHONE,
tblRoster.CELLPHONE, tblRoster.PAGEPHONE, tblRoster.WkHrs, tblRoster.SQUAD,
tblRoster.CallSign, tblRoster.SKILLS, tblRoster.STATUS, tblRoster.GUNMAKE,
tblRoster.GUNMODEL, tblRoster.GUNSERIAL, tblRoster.RADIO,
tblRoster.SPCLEQPT, tblRoster.VEHICLE, tblRoster.ARANK, tblRoster.NextSquad,
tblRoster.NextWatch, tblRoster.NextDO1, tblRoster.NextDO2,
tblRoster.NextDO3, tblRoster.NextDO4, tblRoster.NextSupvID,
tblRoster.NextCallSign, tblRoster.NextWkHrs, tblRoster.LastEval,
Left([Squad],3) AS SquadRoot, Left([NextSquad],3) AS NextSquadRoot

FROM tblRoster RIGHT JOIN dbo_ViewRoster ON tblRoster.ID = dbo_ViewRoster.ID

WITH OWNERACCESS OPTION;

Definitions For qryTemp
SELECT tblTemp.*, dbo_ViewRoster.LAN, dbo_ViewRoster.LANPermission,
dbo_ViewRoster.LName, dbo_ViewRoster.FName, dbo_ViewRoster.MI,
dbo_ViewRoster.Suffix, dbo_ViewRoster.StNum, dbo_ViewRoster.StName,
dbo_ViewRoster.AptNumber, dbo_ViewRoster.City, dbo_ViewRoster.State,
dbo_ViewRoster.Zip, dbo_ViewRoster.HPhone, dbo_ViewRoster.MAPVolunteer,
dbo_ViewRoster.MStNum, dbo_ViewRoster.MStName, dbo_ViewRoster.MApt,
dbo_ViewRoster.MCity, dbo_ViewRoster.MState, dbo_ViewRoster.MZip,
dbo_ViewRoster.CDLNumber, dbo_ViewRoster.CDLType, dbo_ViewRoster.CDLExpDate,
dbo_ViewRoster.BBadge, dbo_ViewRoster.FBadge, dbo_ViewRoster.Rank,
tblRoster.OFCPHONE, tblRoster.CELLPHONE, tblRoster.PAGEPHONE,
tblRoster.SKILLS, tblRoster.GUNMAKE, tblRoster.GUNMODEL,
tblRoster.GUNSERIAL, tblRoster.RADIO, tblRoster.SPCLEQPT, tblRoster.VEHICLE,
tblRoster.LastEval, Left([tblTemp.Squad],3) AS SquadRoot,
Left([tblTemp.NextSquad],3) AS NextSquadRoot

FROM (tblTemp LEFT JOIN dbo_ViewRoster ON tblTemp.ID = dbo_ViewRoster.ID)
LEFT JOIN tblRoster ON tblTemp.ID = tblRoster.ID

WITH OWNERACCESS OPTION;


************************************************** ****
"Allen Browne" <ab***************@bigpond.net.au> wrote in message
news:GC********************@news-server.bigpond.net.au...
More information, please Fred.

Please give an example of the simplest filter that fails, e.g.:
ClientID = 27.
Since you are performing a UNION on other queries, please indicate if the
source fields of the filter are table fields, or calculated fields. If
tables, indicate the field types, e.g.:
tblClient1.ClientID = AutoNumber;
tblClient2.ClientID = Number (Long)
If calculated fields, indicate the calculations e.g.:
Query1.ClientID: 1 + 2
Query2.ClientID: 2 * 2

We may also need information on the queries: outer joins?

Any information you can glean on which fields are missing?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
************************************************** ****
"Fred Zuckerman" <zu********@sbcglobal.net> wrote in message
news:2f**************************@posting.google.c om...
I have a union query joining two other queries, each with 62 fields.

Interestingly, when I open the query it has the correct number of 2850
records. But if I then set a filter (using filter by form), when I
apply the filter I get only 206 records, when I should get 208. If I
then remove the filter I get 2850 records again. But if I then click
on "apply filter" again I get only 186 records.

Each time I remove the filter I get the correct record count. But when
I reapply the filter I get different counts, never correct and never
the same.

But, if I apply the filter to the original two queries and then
perform the union, then I always get the correct count.

Can anyone explain this?
TIA,
Fred Zuckerman
San Diego, CA, USA


Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.