Sum of Quantities in Three Tables

[rmiller]

Greetings to you all

I have been struggling with a problem I thought was simple. However I am unable to solve it. I have searched the forum with no luck.

I have three separate tables that have somewhat similar structures. Each have a Quantity in a specific column that I would like to sum across all three tables. The tables contain hourly values for (say) one week (168 values for each contract type). The table structure is as follows:

T1:
Contract_key1
Effective_Date
Quan1

T2:
Contract_key2
Effective_Date
Quan2

T3:
Unit_key
CC_key
Effective_Date
Quan3

The length of T1 and T2 depends on how many contract types are involved. Table T3 depends on the number of units (Number of rows= 168*No. of contracts(units in T3)).

I would like to write a query (will be in VB eventually) that would give me the Sum(Quan1+Quan2+Quan3) for each hour accross all contracts and units. My output table should have only 168 rows (sum all quantities each hour). In the future I may want to sum for each contract, but currently I only need a single sum for each hour.

If this issue has already been dealt with on this forum please point me in the right direction.

Thanks

rmiller

 

[neileg]

I'm not clear what the realtionship of the three queries is, but I suggest you create a union query to bring them all together and then perform your sum.

Since you can only union data with identical field names and data types, I suggest you first create three select queries that generate this matching format and then create the union.

 

[Rich]

Why not just normalise your structure and save yourself the hassle?

 

[rmiller]

Sum data from three tables

Thanks for the response.

The database that I am trying to manipulate was not developed by me. In fact this is an output database that was produced as output to hourly simulation of my company's operation and there is nothing that I can do to affect the structure of this database. The program is Oracle based but produces an Access (mdb) database of selected parameters that I need to prepare reports for to distribute to various organizations.

I have an existing Access based VBA program that is scheduled to run once per day and will need to automatically extract the information I need, prepare a file, then FTP this file to several people, all without any human intervention.

I can extract most of what I need easily, however, there is one piece of information that I am struggling with and it involves summing one column in each of the three tables. I am in the process of programatically summing the tables individually but wanted to find out if there was a more elegant and efficient way of accomplishing this task.

BTW I am trying to query three TABLES not three queries. I am trying to use a single query (if possible) to accomplish this task.

Any suggestions?

Thanks!

rmiller

 

[neileg]

If you follow my suggestion you will end up with one query. I am guessing that you don't have lots of experience with Access and starting with three select queries and turning them into one union query is the easier way to go.

You would get an SQL query that looks like this

SELECT tbl1.Effective_Date, tbl1.Quan1 AS Quantity
FROM tbl1
union SELECT tbl2.Effective_Date, tbl2.Quan2 AS Quantity
FROM tbl2
UNION SELECT tbl3.Effective_Date, tbl3.Quan3 AS Quantity
FROM tbl3;

 

[rmiller]

Sum of Quantities in three tables

Thanks Neil

You are correct, I am new to Access although I have some experience in programming. I will try this and let you know how it turns out.

Rmiller