query calculation not summing correctly

[glb466]

Hello. I have a Project database that has a query to calculate the quantities that need to be ordered. Items are entered in a subform which populates a table. I created a query that takes the QTY Needed subtracts QTY on hand and Qty on order. The first query pulls the QTY needed from the table and what I want is for it to sum what is needed for each part number. What is happening is for certain items it is generating numbers that are way out of whack.

example:
inventory system shows 175 routers on hand
we need 717 for 3 different projects
we have 598 on order
so it should show that I have 56 left, which would not show up on the TO BE ORDERED REPORT.

What I am getting from the query is 2151. Which is 717*3... why is this happening and how can I fix it?

Thanks in advance!

 

[boblarson]

Sounds like the results of a Cartesian Product (you have two tables, queries, or combination thereof) which has one of them with 3 records being returned and the other with 717 being returned. And if you don't have your tables joined then it multiplies the one times the other to give you one record for each combination of the two. You need to join the two on the correct ID.

 

[glb466]

The tables are joined here is a snapshot of the relationships...

 

[boblarson]

You have other queries in there that can be causing the issue.