Query summing problem

[aero-spaces]

I have a home-grown Inventory Management database that I started before I
knew a whole lot about Access (that's not to say I'm a whiz now), but there's
one issue that I have yet to resolve to my satisfaction--

I have two main tables, a "Receiving" and a "Shipping." I originally created
a query to basically just be the difference between the two to show me the
"Current In Stock" inventory. The parts have lot numbers on them, so I
quickly ran into issues regarding those. So now, I have a "Total Received"
query which just sums the total of every lot we've ever received, and a
"Total Shipped" query which does the same for shipping. Then I started having
issues with location (and still do) where if there was 2 pieces in one
location and 3 pieces (same lot) in another location, it would say there were
5 pieces in both. I created a "Total Locations" query which just shows every
location of each currently in-stock part by lot number. I thought that this
had pretty much fixed the issue since I had not seen it in a long time, but I
just encountered it again today. Here's what it is doing:

If the ACTUAL stock looks like this (IRL):
[Part Number],[Heat Lot],[Qty],[Location]
12345,ABC99,2,SW12
12345,DEF88,4,SW12
12345,ABC99,3,SE55

The query I have pasted below gives me the following results:
12345,ABC99,5,SW12
12345,ABC99,5,SE55
12345,DEF88,4,SW12

Unfortunately, the Part Number and the Lot number are the ONLY pieces of
information common between the two tables. I have pasted the SQL below so you
wizards out there can figure out what I'm doing wrong. If you think it would
be best to start over completely with a different format, I'm open to
suggestions.

Thanks!
Ryan


SELECT [Total Received].[Part Number], [Total Received].[Heat Lot],
Sum([Total Received]![SumOfQty Received]-NZ([Total Shipped]![SumOfQty
Shipped])) AS Qty, [qryTotal Locs].Location
FROM ([Total Received] LEFT JOIN [Total Shipped] ON ([Total Received].[Part
Number] = [Total Shipped].[Part Number]) AND ([Total Received].[Heat Lot] =
[Total Shipped].[Heat/Lot Number])) LEFT JOIN [qryTotal Locs] ON ([Total
Received].[Part Number] = [qryTotal Locs].[Part Number]) AND ([Total
Received].[Heat Lot] = [qryTotal Locs].[Heat Lot])
GROUP BY [Total Received].[Part Number], [Total Received].[Heat Lot],
[qryTotal Locs].Location
HAVING (((Sum([Total Received]![SumOfQty Received]-NZ([Total
Shipped]![SumOfQty Shipped])))>0) AND (([qryTotal Locs].Location) Is Not
Null));

 

[aero-spaces]

I usually don't like to bump threads, but I'm sure someone out there as a
clue as to why my queries are returning the wrong results.

Thanks again in advance for any assistance you can provide!
Ryan