Re: make table query that totals from several tables
- From: "Michel Walsh" <vanderghast@VirusAreFunnierThanSpam>
- Date: Tue, 10 May 2005 15:20:37 -0400
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
"HeatherShores" <HeatherShores@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4712EE19-6353-4261-A6DC-53A004538B9E@xxxxxxxxxxxxxxxx
>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.
.
- References:
- make table query that totals from several tables
- From: HeatherShores
- make table query that totals from several tables
- Prev by Date: Re: limiting query results
- Next by Date: RE: Limiting query return
- Previous by thread: make table query that totals from several tables
- Next by thread: Producing records from nothing
- Index(es):
Relevant Pages
|
