Duplicate Amounts don't show up in Union Query

[Guest]

I have the following SQL but Sales has duplicate values that don't show up in
the result of the query what am I doing wrong

SELECT CashReceiptAmount AS Cash, 0 AS Sales
FROM tblCashReceipts
Where Datepart("M",[CashReceiptDate])=[Month]
GROUP BY CashReceiptAmount
UNION ALL select 0 As Cash, Extension As Sales
FROM qryInvoice
Where Datepart("M",[InvoiceDate])=[Month]
GROUP BY Extension;

 

[Tom Ellison]

Dear Tomcat:

SELECT CashReceiptAmount AS Cash, 0 AS Sales
FROM tblCashReceipts
Where Datepart("M",[CashReceiptDate])=[Month]
GROUP BY CashReceiptAmount
UNION ALL
select 0 As Cash, Extension As Sales
FROM qryInvoice
Where Datepart("M",[InvoiceDate])=[Month]
GROUP BY Extension;

I have formatted your queries above for my own study.

UNION ALL does not, or at least isn't supposed to drop duplicates.

However, GROUP BY does! I do not see why you are grouping these, unless it
was your intent to remove duplicate values.

May I propose some experiments to test this:

Record the number of rows returned by the UNION ALL query.

Paste the SQL from each half of the UNION ALL query into a separate query
and run them. Record the number of rows returned from each. Do they total
the same as the UNION ALL? They should.

Look at the results from these separate queries. Have they already dropped
duplicates? I expect so.

Remove the GROUP BY line from each and look again. Are the duplicates now
being displayed? I expect so.

Make the union again, this time withoug the two GROUP BY lines. Is it now
all fixed? Again, I believe it will be.

Can you now see what is happening, or why?

GROUP BY is supposed to consolidate on the column(s) being grouped. Don't
use it unless that's what you want.

Tom Ellison

 

[Lythandra]

Interestingly enough, I am having the EXACT same problem.

I have 2 separate queries that show the correct data and number of entries.

However like the above poster, when i UNION SELECT them together, my
duplicate entries get dropped.

I have to format the data a certain way for import into our accounting
system so duplicate entries are possible.

Anyone have any thoughts on this or can a Union Query just not handle exact
duplicate entries.

Dear Tomcat:

SELECT CashReceiptAmount AS Cash, 0 AS Sales
FROM tblCashReceipts
Where Datepart("M",[CashReceiptDate])=[Month]
GROUP BY CashReceiptAmount
UNION ALL
select 0 As Cash, Extension As Sales
FROM qryInvoice
Where Datepart("M",[InvoiceDate])=[Month]
GROUP BY Extension;

I have formatted your queries above for my own study.

UNION ALL does not, or at least isn't supposed to drop duplicates.

However, GROUP BY does! I do not see why you are grouping these, unless it
was your intent to remove duplicate values.

May I propose some experiments to test this:

Record the number of rows returned by the UNION ALL query.

Paste the SQL from each half of the UNION ALL query into a separate query
and run them. Record the number of rows returned from each. Do they total
the same as the UNION ALL? They should.

Look at the results from these separate queries. Have they already dropped
duplicates? I expect so.

Remove the GROUP BY line from each and look again. Are the duplicates now
being displayed? I expect so.

Make the union again, this time withoug the two GROUP BY lines. Is it now
all fixed? Again, I believe it will be.

Can you now see what is happening, or why?

GROUP BY is supposed to consolidate on the column(s) being grouped. Don't
use it unless that's what you want.

Tom Ellison

I have the following SQL but Sales has duplicate values that don't show up
in
the result of the query what am I doing wrong

SELECT CashReceiptAmount AS Cash, 0 AS Sales
FROM tblCashReceipts
Where Datepart("M",[CashReceiptDate])=[Month]
GROUP BY CashReceiptAmount
UNION ALL select 0 As Cash, Extension As Sales
FROM qryInvoice
Where Datepart("M",[InvoiceDate])=[Month]
GROUP BY Extension;