Trying to summarize 2 tables

[west]

Hello folks.

I'm trying to combine/summarize two tables in a query. What I've found so far is that I have to use a union query. I tried that but all I get is punctuation error.

So here is what I'm trying to do:

tblProdA
------------------------------------
 ID  | Date           | Production
-----+----------------+-------------
 01  | 1/1/2015       |   10
 02  | 1/3/2015       |   10
 03  | 1/10/2015      |   20
 04  | 1/13/2015      |   20
 05  | 1/17/2015      |   30
------------------------------------


tblProd2
------------------------------------
 ID  | Date           | Production
-----+----------------+-------------
 01  | 1/1/2015       |   5
 02  | 1/5/2015       |   5
 03  | 1/10/2015      |   10
 04  | 1/13/2015      |   10
 05  | 1/15/2015      |   10
 06  | 1/17/2015      |   5
------------------------------------


qrySummary
------------------------------------
 ID  | Date           | Production
-----+----------------+-------------
 01  | 1/1/2015       |   15
 02  | 1/3/2015       |   10
 03  | 1/5/2015       |   5
 04  | 1/10/2015      |   30
 05  | 1/13/2015      |   10
 06  | 1/15/2015      |   10
 07  | 1/17/2015      |   35
------------------------------------

And I uploaded the sample as well.

Thank you very much!

-JC

 

[pbaldy]

Well, your design isn't normalized with different tables for each product. I'd have a single table with a field for product. If you want to keep it that way, the workaround is a union query to join the tables together (but I'd fix the design).

 

[west]

I think I mislead you a bit. Those tables are not meat for products. They are for employee production. I just posted one production item in both for simplification sake, but they will be: [Production] = Item01, and then in the next columns: Item02, Item03 and so on until 46 items. And this has to be repeated for every employee (tblProdA), (tblProdB), etc., up to 16 tables (employees).

But I need a summary table/query for production. With this in mind, does the union query still are my best choice? -thank you! -JC

 

[pbaldy]

I think normalizing the tables comes first:

http://www.r937.com/Relational.html

You don't want fields (columns) for each item, and you don't want tables for each employee.

 

[west]

You were right, Paul. I simplified this by just creating a table with columns:

ID | Employee | Date | Job | ProductionAmount

Thank you very much for pointing me in the right direction and the link.

-JC

 

[pbaldy]

No problem!