Stupid Union Query Misbehaving

[JamesMcS]

Afternoon campers, I have a head scratcher.....

This union query is a bit strange:

SELECT [Full Sales History CY-0 AEM].Month, "AEM" as Region, "CY-0" as Period, [Full Sales History CY-0 AEM].SalesOrg, [Full Sales History CY-0 AEM].Material, [Full Sales History CY-0 AEM].[NS(F)], [Full Sales History CY-0 AEM].COS, [Full Sales History CY-0 AEM].[NC(F)], [Full Sales History CY-0 AEM].[GM%], [Full Sales History CY-0 AEM].Volumes, [Full Sales History CY-0 AEM].Monthsago, [Full Sales History CY-0 AEM].Identifier
FROM [Full Sales History CY-0 AEM];

UNION ALL

SELECT [Full Sales History CY-0 APAC].Month, "APAC" as Region, "CY-0" as Period, [Full Sales History CY-0 APAC].SalesOrg, [Full Sales History CY-0 APAC].Material, [Full Sales History CY-0 APAC].[NS(F)], [Full Sales History CY-0 APAC].COS, [Full Sales History CY-0 APAC].[NC(F)], [Full Sales History CY-0 APAC].[GM%], [Full Sales History CY-0 APAC].Volumes, [Full Sales History CY-0 APAC].Monthsago, [Full Sales History CY-0 APAC].Identifier
FROM [Full Sales History CY-0 APAC];

UNION ALL

SELECT [Full Sales History CY-1 AEM].Month,  "AEM" as Region, "CY-1 Same Period" as Period,[Full Sales History CY-1 AEM].SalesOrg, [Full Sales History CY-1 AEM].Material, [Full Sales History CY-1 AEM].[NS(F)], [Full Sales History CY-1 AEM].COS, [Full Sales History CY-1 AEM].[NC(F)], [Full Sales History CY-1 AEM].[GM%], [Full Sales History CY-1 AEM].Volumes, [Full Sales History CY-1 AEM].Monthsago, [Full Sales History CY-1 AEM].Identifier
FROM [Full Sales History CY-1 AEM]
WHERE (((CDate([Month])) Between CDate("01/01/" & Year(Now())-1) And CDate("01/" & Month(Now())-1 & "/" & Year(Now())-1)));

UNION ALL

SELECT [Full Sales History CY-1 APAC].Month,  "APAC" as Region, "CY-1 Same Period" as Period,[Full Sales History CY-1 APAC].SalesOrg, [Full Sales History CY-1 APAC].Material, [Full Sales History CY-1 APAC].[NS(F)], [Full Sales History CY-1 APAC].COS, [Full Sales History CY-1 APAC].[NC(F)], [Full Sales History CY-1 APAC].[GM%], [Full Sales History CY-1 APAC].Volumes, [Full Sales History CY-1 APAC].Monthsago, [Full Sales History CY-1 APAC].Identifier
FROM [Full Sales History CY-1 APAC]
WHERE (((CDate([Month])) Between CDate("01/01/" & Year(Now())-1) And CDate("01/" & Month(Now())-1 & "/" & Year(Now())-1)));

UNION ALL

SELECT [Full Sales History CY-1 AEM].Month, "AEM" as Region, "CY-1" as Period, [Full Sales History CY-1 AEM].SalesOrg, [Full Sales History CY-1 AEM].Material, [Full Sales History CY-1 AEM].[NS(F)], [Full Sales History CY-1 AEM].COS, [Full Sales History CY-1 AEM].[NC(F)], [Full Sales History CY-1 AEM].[GM%], [Full Sales History CY-1 AEM].Volumes, [Full Sales History CY-1 AEM].Monthsago, [Full Sales History CY-1 AEM].Identifier
FROM [Full Sales History CY-1 AEM];

UNION ALL SELECT [Full Sales History CY-1 APAC].Month, "APAC" as Region, "CY-1" as Period, [Full Sales History CY-1 APAC].SalesOrg, [Full Sales History CY-1 APAC].Material, [Full Sales History CY-1 APAC].[NS(F)], [Full Sales History CY-1 APAC].COS, [Full Sales History CY-1 APAC].[NC(F)], [Full Sales History CY-1 APAC].[GM%], [Full Sales History CY-1 APAC].Volumes, [Full Sales History CY-1 APAC].Monthsago, [Full Sales History CY-1 APAC].Identifier
FROM [Full Sales History CY-1 APAC];

It's supposed to collate sales data from four tables (CY stands for calendar year, so CY-0 is this year, and so on). You'll notice that there are two instances of the CY-1 tables - one pair is grouped by Period "CY-1", the other pair "CY-1 Same Period" - these ones have criteria in which are supposed to limit that part of the list to show sales for the same period as in CY-0 - in this case, up to September. Clear as mud??

Strange thing is, if I run the query as is I get sales of about £2.9 million for CY-1. If I remove the CY-1 Same Period queries, it goes up to £3.9 million. I thought UNION ALL was supposed to allow duplicates in the results? What's going wrong?? Could it be to do with the hugeness of the list? There would probably be about 10 million rows in the result set....

 

[spikepl]

Don't asume - google SELECT UNION ALL

 

[JamesMcS]

Uh huh, I understand how the union query works (I think) - union all allows duplicates which I would certainly expect to see in the result set. What I'm asking is, why would adding the same table in twice, but with criteria in the second select statement, seem to reduce the result set?