Problem with Union Query

[raghavcacs]

Hi i have a union query which picks data from a table and gives me a report.

Total no.of records has to be 400000 rows, but it is showing some where around 360000 rounds, 40000 rows are missing. I don't have any clues on this.

Can any one help me on this?

 

[Joe4]

I believe that Union Queries automatically drop all records which are exact duplicates of other records.

Do you have duplicates in there (when considering all fields)?
Do you not want it to remove duplicates?

 

[raghavcacs]

Joe,
I suspected about duplicates. Yes, It has duplicates in 10 fileds out of a total of 15 fields. But these 10 fields are the deciding factor.

Actually the union query is to convert columns in to rows. The rows deciding factors are those 10 fields.

I want to have the duplicates also. Is there any way?

 

[seenfresh]

Actually the union query is to convert columns in to rows.

Do you mean CROSS TAB QUERY? A Union query combines data from multiple queries/Tables into one related set of records... to convert columns to rows (pivot the data) this would involve a CROSS TAB query I believe.

UNION Query:

http://office.microsoft.com/en-us/access/HA102061091033.aspx

CROSS TAB Query:

http://office.microsoft.com/en-us/access/HA102295771033.aspx

 

[raghavcacs]

The purpose of this query is different that's why I used Union query. It is not pivoting the data.

 

[seenfresh]

To include duplicates in a Union Query use the ALL keyword ie..

SELECT name, price, warranty_available, exclusive_offer
FROM Products
UNION ALL
SELECT name, price, guarantee_available, exclusive_offer
FROM Services;