query doubles stock, and i do not know why

[Traden]

HI,
i do have that query for the current stock. IT doubles the numbers for receipt and issue, so stock is not correct, and i do not why. Maybe someone has a clue?

SELECT [Inventory Control].Lot, [Inventory Control].[Storeage Location], [Inventory Control].Description, Sum([Inventory Control]![Quantiy reciept]-[Material Issue]![Quantity]) AS stock, [Material Master].[Unit of measure]

FROM [Material Master] INNER JOIN ([Inventory Control] LEFT JOIN [Material Issue] ON [Inventory Control].ID = [Material Issue].ID) ON [Material Master].ID = [Inventory Control].ID

GROUP BY [Inventory Control].Lot, [Inventory Control].[Storeage Location], [Inventory Control].Description, [Material Master].[Unit of measure];


thanks

 

[RobJ]

did you use a function like SUM, /, +, - or * in one of your tables/queries?

That could be the reason.

 

[Freddy]

I wish my stock would double. You wouldn't happen to work for a stock exchange? lol.

Run your query without the sums. Check for duplicate records. It sometimes helps to bring is more, if not all the fields from the tables to see where dupes are coming from.

 

[Vassago]

Try doing a group by or splitting the query into two. This usually helps when a query is too complex.

 

[Traden]

thanks

Thanks all

Pat:

with the 2 querys it works, roughly! But know i have a new problem. how do i get stock for material that is not issued jet? In my prior Version i solved that with an Nz() on the stock values, but now that doesn`t work anymore..

thanks again