Union Query Issue

  • Thread starter Thread starter Larry
  • Start date Start date
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
 
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,

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...
 
Back
Top