Union Query Issue

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 Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Larry" wrote in message
...
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

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" wrote in message
...
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
http://I.Am/DougSteele
(no e-mails, please!)



"Larry" wrote in message
...
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

Hi Larry,

I wonder if you shouldn't use

UNION ALL

instead of

UNION

"Larry" wrote:

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

Try 'Union All'. union is only matching records