Union Query Dropping Records

[Bedsingar]

Hello,

I have a problem with one of my union queries which seems to be dropping records and I can't understand why...

Code for the Query is:

SELECT *
FROM [APPROVED_FIA_Excluding Prior Years]
UNION SELECT *
FROM [Format_Prior_Years_Actuals]
UNION SELECT *
FROM [Format_FIA_DATA_2]
UNION SELECT *
FROM [FORMAT_SEED]
UNION SELECT *
FROM [CAP_ROLLOVER_P1]
UNION SELECT *
FROM [CAP_ROLLOVER_P2]
UNION SELECT *
FROM [REV_ROLLOVER_P1]
UNION SELECT *
FROM [REV_ROLLOVER_P2];

Resultant Record Count = 12403

[APPROVED_FIA_Excluding Prior Years] =891
[Format_Prior_Years_Actuals] =4416
[Format_FIA_DATA_2] = 7736
[FORMAT_SEED]=96
[CAP_ROLLOVER_P1]=7
[CAP_ROLLOVER_P2]=7
[REV_ROLLOVER_P1]=6
[REV_ROLLOVER_P2]=6

So resultant count should be 13165 meaning I've somehow lost 762 records.



Can anybody explain why this is happening??

Thanks

Josh

[Bedsingar]

Oh ok I see... Union ALL is what I needed Solved

[alansidman]

For others viewing this thread, this link will explain the difference between a Union Query and a Union All Query.

http://office.microsoft.com/en-us/ac...010206109.aspx

Alan