Inner Join query generating wrong result

[sahafi]

My query is based on 2 tables (pounds & Hours). Both tables use autonumber as
a PK. Both tables share 5 similar fieldsYear, Month, Week, Location,
ProdCategory) but as you can see none of those fields is unique. The pound
table has additional field (ProdCode) by which the pounds are grouped.
In the query I need to show only the Year (no month/week), Location,
ProdCategory, and SumOfPounds form the pound table, plus the (Hours) from the
hours table. When I group all 4 fields and sum the pounds field, I get a
large data (as if i'm pulling weekly data). When I sum the hours as well as
the pounds I get a wrong result (way larger numbers for both fields than it
shoud be).
How can I get sum of pounds/hours for each ProdCategory at each Location for
the year as one number?

Thanks.

 

[John Spencer]

I would suggest a query with sub-queries as the source, but I am fearful
that it wouldn't work with your field names

SELECT Year, Location, ProdCategory, Sum(Pounds) asTotalPounds
FROM Pounds
GROUP BY Year, Location, ProdCategory

SELECT Year, Location, ProdCategory, Sum(Hours) asTotalHours
FROM Hours
GROUP BY Year, Location

Then using those two queries

SELECT P.Year, P.Location, P.ProdCategory
, P.TotalPounds, H.TotalHours
FROM QueryOne as P INNER JOIN QueryTwo as H
On P.Year = H.Year
and P.Location = H.Location
and P.ProdCategory = H.ProdCategory

All in one if your field names and table names consist of only letters,
numbers, and underscore characters might be as follows.

SELECT P.Year, P.Location, P.ProdCategory
, P.TotalPounds, H.TotalHours
FROM (
SELECT Pounds.Year, Pounds.Location, Pounds.ProdCategory
, Sum(Pounds.Pounds) asTotalPounds
FROM Pounds
GROUP BY Pounds.Year, Pounds.Location, Pounds.ProdCategory
) as P INNER JOIN
(
SELECT Hours.Year, Hours.Location, Hours.ProdCategory
, Sum(Hours.Hours) asTotalHours
FROM Hours
GROUP BY Hours.Year, Hours.Location
) as H
On P.Year = H.Year
and P.Location = H.Location
and P.ProdCategory = H.ProdCategory

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[sahafi]

John,
I used the first option, and it works perfectly. Thank you so much.


Os.