Limiting results from one side of a join conditionally

[Guest]

Hi, I have a query that is causing me some grief. I have a table
(Subinventory Onhand with Cost) that contains a load of data, and the
essential fields are itemNumber and LotCode. I also have another table
(salesdata) that tracks sales for the past 12 months by month and item
number. I want to join them together, but I only want the sales totals to
show up one time, even though there may be multiple listings of the same item
number (because of differing production lots). The query works but again, if
I have three lots for an item, the totals are calculated three times. If
anyone can provide any help, I'd appreciate it greatly. Thanks

Explanation:
[Subinventory Onhand with Cost] Large table with listing by location of items.
[salesdata] Listing of sales by location and item number
..Subinv - The name of the location
lstQuery.Column(0, idx) - The current location selected

The query:


select [Subinventory Onhand with Cost].*,
(m1+m2+m3+m4+m5+m6+m7+m8+m9+m10+m11+m12) as
TotalSales,totalsales*MgmtCostEach as MgmtSales,totalsales*ListpriceEach as
ListSales FROM salesdata RIGHT JOIN [Subinventory Onhand with Cost] ON
([salesdata].[subinv] = [Subinventory Onhand with Cost].[subinv] and
[salesdata].[itemnumber] = [Subinventory Onhand with Cost].[itemnumber])
where [Subinventory Onhand with Cost].[SubInv] = """ & lstQuery.Column(0,
idx) & """ order by [Subinventory Onhand with Cost].ItemNumber,LotCode ASC

 

[Michel Walsh]

Make the sum outside any join, join after that.


SELECT myTable.*, x.theSum
FROM myTable INNER JOIN ( SELECT productID, SUM(value) AS theSum
FROM somewhere
GROUP BY productID) AS
x
ON myTable.ProductID =x.productID




Hoping it may help,
Vanderghast, Access MVP