Union Queries & Filters - Microsoft Access / VBA

Fred Zuckerman

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

Subscribe Reply

3833

Allen Browne

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.Clie ntID = AutoNumber;
tblClient2.Clie ntID = 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********@sbc global.net> wrote in message
news:2f******** *************** ***@posting.goo gle.com...

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

Fred Zuckerman

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.SUPVI D, tblRoster.OFCPH ONE,
tblRoster.CELLP HONE, tblRoster.PAGEP HONE, tblRoster.WkHrs , tblRoster.SQUAD ,
tblRoster.CallS ign, tblRoster.SKILL S, tblRoster.STATU S, tblRoster.GUNMA KE,
tblRoster.GUNMO DEL, tblRoster.GUNSE RIAL, tblRoster.RADIO ,
tblRoster.SPCLE QPT, tblRoster.VEHIC LE, tblRoster.ARANK , tblRoster.NextS quad,
tblRoster.NextW atch, tblRoster.NextD O1, tblRoster.NextD O2,
tblRoster.NextD O3, tblRoster.NextD O4, tblRoster.NextS upvID,
tblRoster.NextC allSign, tblRoster.NextW kHrs, tblRoster.LastE val,
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.OFCPH ONE, tblRoster.CELLP HONE, tblRoster.PAGEP HONE,
tblRoster.SKILL S, tblRoster.GUNMA KE, tblRoster.GUNMO DEL,
tblRoster.GUNSE RIAL, tblRoster.RADIO , tblRoster.SPCLE QPT, tblRoster.VEHIC LE,
tblRoster.LastE val, Left([tblTemp.Squad],3) AS SquadRoot,
Left([tblTemp.NextSqu ad],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******** ************@ne ws-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.Clie ntID = AutoNumber;
tblClient2.Clie ntID = 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********@sbc global.net> wrote in message
news:2f******** *************** ***@posting.goo gle.com...
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

Similar topics

12843

"Union all" and "UNION" ???? Return the same result or not ??

by: KENNY L. CHEN | last post by:

Dear experts, I have two tables in my Oracle 8i database: TEST (COL1,COl2,REC_NO) and TEST1 (COL1,COL2,REC_NO). Both tables are unique-indexed on (COL1,COL2,REC_NO). I think the following SQL commands will return the same result but one of my friends don't think so. He said "QUERY 1" will return 1 unsorted record (ROWNUM < 2 ) first then sort the result (ORDER BY COL1 ASC,

Oracle Database

5801

Performance of union all views

by: lyn.duong | last post by:

Hi, I have a large table (about 50G) which stores data for over 7 years. I decided to split this table up into a yearly basis and in order to allow minimum changes to the applications which access this table, I created a union all view over the 7 yearly tables. What I have noticed is that queries against the union all view is considerably slower than queries against the original table. When I ran db2batch, I noticed cpu usage was higher...

DB2 Database

3035

SQL0181N on nickname for union-all-view

by: _link98 | last post by:

Problem: getting SQL0181N for queries on nicknames to remote Union-All-View. Can't see what I'm doing wrong yet, maybe someone has seen this before. Environment: UDB ESE 8.1 + FIXPAK 9A, on Solaris 8.1, with 64-bit instances. I have a NICKNAME called REMOTE_DW.T_MYTABLE which points to a "union-all-view". Several other nicknames exist to regular-tables and regular views in

DB2 Database

3959

Access 2000 union query fails silently or with "data type mismatch in criteria expression"

by: s_gregory | last post by:

The mdb is considerable size 70 +- mb. A complex union query was working well, but when an additional union select... was added into the query, selecting identical fields from a different source, it began to have problems. Failure was observed only in a few PC's at first. For example, in an NT 4.0 SP6 PC, it continued to work OK. But in my Win 2k laptop, it failed. As the union query was gradually simiplified in testing, the failure...

Microsoft Access / VBA

1922

Need some help fixing my UNION statement

by: Alicia | last post by:

Hello everyone based on the data, I created a union query which produces this. SELECT ,,, 0 As ClosedCount FROM UNION SELECT ,, 0 AS OpenedCount, FROM ORDER BY , ;

Microsoft Access / VBA

2939

when or Where do you use a union query ?

by: shaun palmer | last post by:

when or Where do you use a union query ? how can I wright sql, tblPopulation,* tblcustomer,* one to one with all the appropriate attributes(field). Your help would be greatly appreciated. Thank you.

Microsoft Access / VBA

2915

Using UNION of 2 or more saved queries

by: Susan Bricker | last post by:

For those of you who have been following my posts - they all pertain to a Dog Competition Organization's Database. There are three classes that the dogs can participate: NOVICE, OPEN, and UTILITY. I want to produce a report of the top 10 average scores for each class for each year.

Microsoft Access / VBA

2224

2 queries OK by themselves, FAIL in Union

by: mikes | last post by:

I have 2 separate queries, which effectively are the same except they draw data from separate tables. Both tables are (design-wise) identical, only the data is different. for each query, there are 2 tables with a standard LEFT JOIN. One field of the query is calculated, looking for a NULL in one table, and then using a field from the second table in that case. One query looks like this: PARAMETERS Text ( 255 ); SELECT...

Microsoft Access / VBA

5064

ADO and creating Union queries

by: BillCo | last post by:

I've encountered a problem while using ADO to save query objects. Union queries created normally (via the interface) appear in adox catelog.procedures rather than catelog.views. This is reasonably well documented and I can live with it. The below seems to be the only way to add queries to a database using ADO: Set cmd = New ADODB.Command cmd.CommandType = adCmdText

Microsoft Access / VBA

9646

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

10346

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

10096

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

8982

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

6742

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

5386

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration

5514

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

4055

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

2887

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General