make table query that totals from several tables

[Guest]

I am stumped here. I am setting up a make table query titled 'total calls
lost' The fields are date and calls lost.

I have 6 tables shift 0, shift 1, shift 2, shift 3, shift 4, shift 5. They
each have date and calls lost columns.

I need to make a total of all the calls lost per per day for any range of
dates specified by the user.

 

[Michel Walsh]

Hi,


Normalize your data with the following query:


SELECT 0 as Shift, [date], CallLost FROM shift_0
UNION ALL
SELECT 1, [date], CallLost FROM shift_1
UNION ALL
....
UNION ALL
SELECT 5, [date], CallLost FROM shift_5



Then,

SELECT Month([date]), shift, SUM(CallLost)
FROM savedQuery
GROUP BY Month([date]), shift


as example, produce the desired stat, quite simply, by month, by shift.


Working with normalized data always help to produce simple SQL statements.
In your case, using 6 different tables for the same kind of data, where
information (the shift) is store not as DATA but as part of the table NAME
is not really appropriate. SQL works with DATA, not with encoded
information... held in a table name. The first query retrieves the
information back as data, and in one "table" (the query). You can make that
query a permanent table (create table), so you can benefit of indexes (on
shift and [date]), if appropriate.



Hoping it may help,
Vanderghast, Access MVP