Union Query Issue

L

Larry

Hi guys.

I'm using Access 97 (SP2) to do some work for a client.

I am running a Union Query on two tables tht are identical, to create one
set of data. Here is the syntax of the query:

SELECT Drills.Date, [Drills Subtable].MemberId
FROM Drills LEFT JOIN [Drills Subtable] ON Drills.DrillID = [Drills
Subtable].DrillID
WHERE (((Drills.Date) Between [Forms]![SummaryReport]![startdate] And
[Forms]![SummaryReport]![enddate]));

UNION SELECT [a Drills].Date, [a Drills Subtable].MemberId
FROM [A Drills] LEFT JOIN [a Drills Subtable] ON [a Drills].DrillID = [a
Drills Subtable].DrillID
WHERE ((([a Drills].Date) Between [Forms]![SummaryReport]![startdate] And
[Forms]![SummaryReport]![enddate]));

---------------------------------------
The table called Drills is the current table. The table called [a drills]
is an archive table.

There are currently records in the drills table, but no records in the
archive table.

If I run the query by ommitting the union statements, a particular member
will list 9 records (I verified in the tables that this was correct). If I
run the full query listed above, that same member lists only 5 records.
Does anyone have an idea what may be causing this?

Thanks,

Larry
 
D

Douglas J. Steele

Are there duplicates? UNION eliminates duplicates.

To avoid having duplicates eliminated, use UNION ALL instead.

BTW, Date is not a good name for a field. It's a reserved word, and can lead
to problems.
 
L

Larry

Doug,

Adding the ALL statement fixed the problem. Turns out that ome of the
members had attended multiple drills on the same day, and since I was only
capturing memberid and date to do the count, the union query was eliminating
the duplicates.

Thanks for the help,

Larry

Douglas J. Steele said:
Are there duplicates? UNION eliminates duplicates.

To avoid having duplicates eliminated, use UNION ALL instead.

BTW, Date is not a good name for a field. It's a reserved word, and can lead
to problems.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Larry said:
Hi guys.

I'm using Access 97 (SP2) to do some work for a client.

I am running a Union Query on two tables tht are identical, to create one
set of data. Here is the syntax of the query:

SELECT Drills.Date, [Drills Subtable].MemberId
FROM Drills LEFT JOIN [Drills Subtable] ON Drills.DrillID = [Drills
Subtable].DrillID
WHERE (((Drills.Date) Between [Forms]![SummaryReport]![startdate] And
[Forms]![SummaryReport]![enddate]));

UNION SELECT [a Drills].Date, [a Drills Subtable].MemberId
FROM [A Drills] LEFT JOIN [a Drills Subtable] ON [a Drills].DrillID = [a
Drills Subtable].DrillID
WHERE ((([a Drills].Date) Between [Forms]![SummaryReport]![startdate] And
[Forms]![SummaryReport]![enddate]));

---------------------------------------
The table called Drills is the current table. The table called [a drills]
is an archive table.

There are currently records in the drills table, but no records in the
archive table.

If I run the query by ommitting the union statements, a particular member
will list 9 records (I verified in the tables that this was correct).
Click to expand...
If
I
run the full query listed above, that same member lists only 5 records.
Does anyone have an idea what may be causing this?

Thanks,

Larry
Click to expand...
Click to expand...
 
Top