Union

[aerosmith]

Im having a big problem with a union, I thought all was well, until i noticed the amount of entires does not match,


SELECT
[D-Report-3-Code1-Final].[Created_By],
[D-Report-3-Code1-Final].[Rcode],
[D-Report-3-Code1-Final].[Amount]
FROM [D-Report-3-Code1-Final]


Gives me 972

SELECT
[D-Report-3-Code1-Final].[Created_By],
[D-Report-3-Code1-Final].[Rcode],
[D-Report-3-Code1-Final].[Amount]
FROM [D-Report-3-Code1-Final]
UNION SELECT
[D-Report-3-Code2-Final].[Created_By],
[D-Report-3-Code2-Final].[Rcode],
[D-Report-3-Code2-Final].[Amount]
FROM [D-Report-3-Code2-Final];


Gives me 588
Im not sure why, I have 3 queries which i have to gather info from to create a report basically, info from 3 queries have the same columns just contain different information.

 

[MSAccessRookie]

Im having a big problem with a union, I thought all was well, until i noticed the amount of entires does not match,


SELECT
[D-Report-3-Code1-Final].[Created_By],
[D-Report-3-Code1-Final].[Rcode],
[D-Report-3-Code1-Final].[Amount]
FROM [D-Report-3-Code1-Final]


Gives me 972

SELECT
[D-Report-3-Code1-Final].[Created_By],
[D-Report-3-Code1-Final].[Rcode],
[D-Report-3-Code1-Final].[Amount]
FROM [D-Report-3-Code1-Final]
UNION SELECT
[D-Report-3-Code2-Final].[Created_By],
[D-Report-3-Code2-Final].[Rcode],
[D-Report-3-Code2-Final].[Amount]
FROM [D-Report-3-Code2-Final];


Gives me 588
Im not sure why, I have 3 queries which i have to gather info from to create a report basically, info from 3 queries have the same columns just contain different information.

Ther UNION Command will Select Records and eliminate any duplicate records that are found as the sets are joined. If you want/need to see these duplicates, try using UNION ALL instead. This will join the sets without removing any of the duplicates.

 

[aerosmith]

Wicked !