Sum Query Delivers wrong results

[rbf]

I'm pretty new to access, but my boss wants me to try and consolidate our massive folder of monthly excell data into a database. So far, I have a few different tables with production data in them. I'm trying to get a daily total (I don't even know if this is the best way to do it) using a sum query. I started by writing a separate query for each table, and it worked fine. When I combine them, the numbers are totally wrong. 10 to 50 x larger than they should be.

Here is the original query, which works fine:

SELECT Bench.Date, Sum(Bench.Total_ft) AS Bench_Feet
FROM Bench
GROUP BY Bench.Date

There are three of these, which all work. This is the combined query:

SELECT Daily_TG.Date, NZ(Sum(Bench.Total_ft),0) AS Bench_Feet, NZ(Sum([Holes_L]+[Holes_R]),0) AS Jumbo_Holes, NZ(Sum(Bolters.Bolts_8),0) AS 8_ft_bolts
FROM Bench, Jumbos, Bolters, Daily_TG
GROUP BY Daily_TG.Date;


Note: Daily_TG.Date is tied in a one to many relationship with the date field in the other 3 tables.



Any thoughts? I've been fumbling through this for 3 days now.

[June7]

There is no JOIN clause in that query. This results in Cartesian joining, every record of every table will join to every record of other tables.

Do all tables have the same dates? If so, pick one table to be the 'primary' and join the other 3 to it. If tables have different dates, will need a dataset that has all possible dates then join the 4 summary queries to that master dataset. The master can be created by a UNION query. There is no wizard or designer for UNION, must type or copy/paste in SQL View.

SELECT [Date] FROM Daily_TG
UNION SELECT [Date] FROM Bench
UNION SELECT [Date] FROM Jumbos
UNION SELECT [Date] FROM Bolters;


BTW, Date is a reserved word, should not use reserved words as names.

[rbf]

Daily_TG has one record for each date. I that is the primary key. I have renamed each field from "Date" to "Day". There will be multiple records for each day in each of the 3 tables (Bolters, Jumbos, Bench).

I have the inner join working for one table (bolters), I'm just not sure how to word it in order to get all of the tables joined by the day fields.

SELECT Daily_TG.Day, Nz(Sum(Bench.Total_ft),0) AS Bench_Feet, Nz(Sum([Holes_L]+[Holes_R]),0) AS Jumbo_Holes, Nz(Sum(Bolters.Bolts_8),0) AS 8_ft_bolts
FROM Daily_TG INNER JOIN Bolters ON Daily_TG.Day = Bolters.Day
GROUP BY Daily_TG.Day

[June7]

Query joining multiple 1-to-many tables will not work. The aggregates must result in one record for each day.

[rbf]

Ok. Is the best way to do this to write three separate join / sum queries, appending the results to three separate tables, then one query that simply pulls the records from that table into a daily summary?

[June7]

Appending to tables not necessary. From my post#2: join the 4 summary queries to that master dataset