query doubles stock, and i do not know why (1 Viewer)

[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.

 

[Pat Hartman]

The problem is conceptual. You have receipts in one table and issues in another. Each of these tables is related 1-to-many to the MaterialMaster but they are not related to each other. If you remove the Sum() and group by and look at the resultset produced by the join, you'll see that each receipt is matched to each issue. This of course does not make sense logically but all RDBMS' will allow you to make joins of this type even though they produce cartesian products which are not very useful.

You can solve the problem in either of two ways. One way is to create two queries. One to sum() the transactions from the receipts table and the other to sum the issues table. Once these tables have been reduced to one row per MaterialMaster record, you can join the two queries to the MaterialMaster table and subtract the issues from receipts as you are doing. The alternative solution is to use a union query to union the transactions from the receipts table to the transactions from the issues table. Then join the union to the MaterialMaster and sum the + and - transactions.

 

[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

 

[Pat Hartman]

I don't see an Nz() in your SQL so I'm not sure what you're talking about. You actually need three queries. Two that sum the records from the transaction tables and a third that joins the summed records and finds the difference. To make sure that you have a record for every material master record, use left joins to the material master table in the two sum queries. Then you can use an inner join in the subtraction query since both recordsets involved in the join will contain the same set of records.