Add the Sum of three columns from different tables

[jeffrey.bergstedt]

I have an Access database (2003) that contains three tables, HRD WEEKLY
DAY, NVA WEEKLY DATA, ROA WEEKLY DATA. There are a large number of
fields in each table(all tables have same fields), but her is an
example based on a couple fields that I need a query for. Fields:

SLA PERIOD, WEEK BEGIN, WEEK END, RES LNP INCOMING ORDER VOLUME

Now, depending on how many weeks there are in any give SLA Period,
there could be 4 or 5 rows with the same SLA Period entry in each
table, but with different WEEK BEGIN and WEEK END. I need an SQL query
that will Sum all the rows in each table where SLA PERIOD = JUL06, then
Sum the Sums.

In other words:

Sum(Sum[RES LNP INCOMING ORDER VOLUME].[HRD WEEKLY DATA] + SUM[RES LNP
INCOMING ORDER VOLUME],[NVA WEEKLY DATA] + SUM[RES LNP INCOMING ORDER
VOLUME].[ROA WEEKY DATA]) where [SLA PERIOD] = "JUL06"

Can anyone help?

Jeff

 

[Rick B]

"all tables have the same fields"?

Typically, relational database normalization states that you don't include
data in fields names, or in table names. To normalize your database, you
would have ONE table called "weekly data". You would add a new field to
store the value HRD, NVA, or ROA.

Until you normalize your date, it will be difficult for you to get what you
want out of the database.

I believe, though I'm not positive, that you could build a UNION query to
pull all the data into one query and then work out the sums you need.

 

[belkingold]

Each of these tables holds numeric (mostly) data for three different
offices that operate independently (to a point). If I were to use one
table, it would have some 200 columns, like

HRD NEW ORDER VOLUME, NVA NEW ORDER VOLUME, ROA NEW ORDER VOLUME...

This would make tracking by each office very difficult and data entry
error prone. I could perform one query oneach database (from Excel),
open a recordset, assign the values (sums) from the recordset to sums,
and add the three variables together and assign to a fourth variable,
but that is a whole lot of VBA.

Surely there must be a way to Sum everything up in a query.

Jeff

 

[Rick B]

No, field names should not contain data.

Your field name would simply be NEW ORDER VOLUME. Another field in that
table (OFFICE) would contain the office initials.

 

[belkingold]

Wish I'd thought of that. I made the change and brought everything in
to one table, fortunately we hadn't begun migrating data yet. Thanks
for your help!


Jeff