Union query loses duplicate record 1

[shaunacol]

I have a union query which lists salesperson, GP and date of sale. There are 2 salespeolpe fields so this query unions salesperson1, GP and date of sale with salesperson2, GP and date of sale. The problem I have that there is occasionally duplicate information (for example a salesperson would have the exact GP and exact date listed twice (just because he happened to have the same GP for 2 particular sales on the same date)). My union query does not recognise this and one of the record just dissapears. Is there any way I can prevent this? I thought that perhaps if I sum the GP field then it will be added together instead of unioned but I don't think this is possible in a union query?

 

[Remou]

I believe UNION ALL is what you want.

http://lessthandot.com

 

[shaunacol]

It doesnt work for me but maybe I am doing it wriong? My query is below - I added the ALL in as below:


SELECT ALL [CustomerID] AS [Customer], [DateOfSale], [TUrnover], [GP], [SalesOrderNo], [InvoiceNumber], [DateInvoicePaid], [SalesPerson1], [Sales1GP]
FROM [CommQry1]

UNION SELECT ALL [CustomerID] AS [Customer], [DateOfSale], [TUrnover], [GP], [SalesOrderNo], [InvoiceNumber], [DateInvoicePaid], [SalesPerson1], [Sales1GP]
FROM [CommQry2];

UNION SELECT ALL [CustomerID] AS [Customer], [DateOfSale], [TUrnover], [GP], [SalesOrderNo], [InvoiceNumber], [DateInvoicePaid], [SalesPerson1], [Sales1GP]
FROM [CommQry3];

 

[Remou]

How about:

SELECT [CustomerID] AS [Customer], [DateOfSale], [TUrnover], [GP], [SalesOrderNo], [InvoiceNumber], [DateInvoicePaid], [SalesPerson1],  [Sales1GP]
FROM [CommQry1]

UNION ALL  
SELECT [CustomerID] AS [Customer], [DateOfSale], [TUrnover], [GP], [SalesOrderNo], [InvoiceNumber], [DateInvoicePaid], [SalesPerson1], [Sales1GP]
FROM [CommQry2];

UNION ALL 
SELECT [CustomerID] AS [Customer], [DateOfSale], [TUrnover], [GP], [SalesOrderNo], [InvoiceNumber], [DateInvoicePaid], [SalesPerson1], [Sales1GP]
FROM [CommQry3];

http://lessthandot.com

 

[shaunacol]

THAT WORKS GREAT - THANK YOU!!!!

 

[dhookom]

shaunacol,
If remou's answer is worth shouting about, how about clicking the link "Thank Remou vor this valuable post!" to recognize a helpful post.

Duane
Hook'D on Access
MS Access MVP