Union query

[lloyd2002]

Hi all,

I have 8 queries which are counting a number of fields from other tables to give me a running total on each segment.

I want to put all of this information in one place, have tried using a report but that wasn't possible as the queries aren't linked. I've gone to use a Union Query which looks like this:-

SELECT Count(T_Daily_Light.[Daily Light]) AS [CountOfDaily Light]
FROM T_Daily_Light
UNION SELECT Count(T_Daily_Medium.[Daily Medium]) AS [CountOfDaily Medium]
FROM T_Daily_Medium
UNION SELECT Count(T_Daily_Heavy.[Daily Heavy]) AS [CountOfDaily Heavy]
FROM T_Daily_Heavy
UNION SELECT Count(T_EuroCargo_Light.[EuroCargo Light]) AS [CountOfEuroCargo Light]
FROM T_EuroCargo_Light
UNION SELECT Count(T_EuroCargo_Medium.[EuroCargo Medium]) AS [CountOfEuroCargo Medium]
FROM T_EuroCargo_Medium
UNION SELECT Count(T_Heavy_Rigid_On_Road.[Heavy Rigid On Road]) AS [CountOfHeavy Rigid On Road]
FROM T_Heavy_Rigid_On_Road
UNION SELECT Count(T_Heavy_Rigid_Off_Road.[Heavy Rigid Off Road]) AS [CountOfHeavy Rigid Off Road]
FROM T_Heavy_Rigid_Off_Road
UNION SELECT Count(T_Heavy_Artic.[Heavy Artic]) AS [CountOfHeavy Artic]
FROM T_Heavy_Artic;

I want to be able to distinguish each of the SELECT rows so I know where the data has come from, or find a way to ensure the the output is always in the same format.

Many thanks.

 

[sheetspread]

SELECT Count(T_Daily_Light.[Daily Light]) AS [CountOfDaily Light], "T_Daily_Light" as SourceTable
FROM T_Daily_Light

.....etc is the easiest way if you don't have too many to type, otherwise with VBA

 

[lloyd2002]

Perfect - many thanks