Query sum returning wrong value

[MrBologna]

I have a query that pulls data from 2 tables: Open_Items_List & Jobs_Parts.
Open items is a table of all current PO's, with part numbers and quantity ordered. There may be duplicates in this table, so quantity ordered must be summed.
Jobs_Parts is a table of the parts required for every active job in production.

The final query must have the following fields:
Qty_Needed : number of that part required: Comes from jobs_Parts
Qty_Ready : number of those parts currently dedicated to that job: manually input
Qty_still_needed: Qty_Needed - Qty_Ready (calculated field in jobs_Parts)


Qty_On_Hand: the sum of all Qty_Ready
Qty_On_Order: the sum of quantity ordered in Open_Items
Qty_Short: (((sum of all Qty_Needed) - sum of all Qty_on_Hand) - Sum of qty_on_order)

These sums should be specific to each part number, and show up for each item in Jobs_Parts.

I started with trying to do it all in one step, but have now simplified to just getting part data.

for better reference:

The excel sheet I am attempting to duplicate:


The first query I have written to get data for each part (returning wrong values):

SELECT Open_Items_List.Part_No, Open_Items_List.Part_Description, Sum(Open_Items_List.Qty_Ordered) AS Qty_On_Order, Sum(Jobs_Parts.Qty_Ready) AS Qty_On_Hand, Sum(Jobs_Parts.Qty_Still_Needed) AS Qty_Short
FROM Open_Items_List 
INNER JOIN Jobs_Parts ON Open_Items_List.Part_No = Jobs_Parts.Part_No
GROUP BY Open_Items_List.Part_No, Open_Items_List.Part_Description;

[pbaldy]

How is that returning the wrong values (what are the detail values, what is the sum, etc)? That should be the sum grouped by part and description. Can you attach a sample db here?

[MrBologna]

Here is how I tested that query:
What was input:




What it returned:


And here it is further separated (not what I want, but may help diagnose):



I would like to see if I can resolve this quickly before I go through and take the time to make a sample database, as this database has a significant amount of private information.

[pbaldy]

You've joined 2 tables there. Are there multiple records in both of them? The expectation would be that there is a single record in one, potentially multiple in the other.

[MrBologna]

Aaah okay, I separated it into 3 queries (2 to do sums and 1 to combine) and it seems to be working correctly now. Thank you!

[pbaldy]

Glad you got it working.

[MrBologna]

However, now I cannot make edits to the Qty_Ready field now. I don't want to have to make that a popout edit window, as it's going to be the main feature of the form.
Is it possible to make this do what I want it to? or will I have to separate them with a subdatasheet?
I would rather avoid the latter, as opening and closing the expansion plus to find that info could get obnoxious when you're doing it constantly throughout each day.

[pbaldy]

Well, a totals query is read-only. You can't edit summed data, only the detailed data that makes it up. If you were able to edit summed data, which record(s) would it update?

[MrBologna]

The Qty_Ready field is not a summed field, it is an input field in Jobs_Parts.
My final query basically displays the Jobs_Parts table, with the extra calculated fields stuck on the end.

[June7]

Review http://allenbrowne.com/ser-61.html. Your query should hit one or more of this conditions.