• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 211
  • Last Modified:

Trouble with SUM in Access 2007 query

Gurus,

I have the included query with which I'd like the SumOfShipQty to show 300, the sum of all shipments for that Job No. I seem to just be getting the sum of that one shipment record which is pointless.
SELECT Jobs.JOBNO, Jobs.QTY, Shipments.DueDate, Shipments.ShipDate, Shipments.ShipQty, Sum(Shipments.ShipQty) AS SumOfShipQty
FROM Shipments INNER JOIN Jobs ON Shipments.Job_ID = Jobs.JOBNO
GROUP BY Jobs.JOBNO, Jobs.QTY, Shipments.DueDate, Shipments.ShipDate, Shipments.ShipQty
HAVING (((Jobs.JOBNO)=42348));

Open in new window

Query.jpg
0
Michaelj42
Asked:
Michaelj42
  • 3
  • 3
1 Solution
 
danishaniCommented:
Try something like this;

SELECT Jobs.JOBNO, Sum(Shipments.ShipQty) AS SumOfShipQty
FROM Shipments INNER JOIN Jobs ON Shipments.Job_ID = Jobs.JOBNO
GROUP BY Jobs.JOBNO
HAVING (((Jobs.JOBNO)=42348));

HTH,
Daniel
0
 
Michaelj42Author Commented:
This works if I add no other fields, but as soon as I add a field like ShipDate where each row is different, I'm back to 50 and 250 in the SumofShipQty
0
 
danishaniCommented:
Yes that is logical to the fact you have two different shipdates, so Access totals then for 2 different records.

So do you want all the information still there with the Grand Total included?

HTH,
Daniel
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Michaelj42Author Commented:
Yes, the questions is, in the screen shot above, is it possible for the SumofShipQty to sum the various shipments for a particular job number. Basically both rows reading 300.

Ultimately where I'm headed is a query that shows jobs with multiple shipments but that are not yet complete. I need the total shipped to see if it is < the qty needed.
0
 
danishaniCommented:
Ok, create a seperate Query with only JOBNO and SumOfShipQty, then create another Query where you add the created SumQuery to it, something like this;      

SELECT Jobs.JOBNO, Jobs.QTY, Shipments.DueDate, Shipments.ShipDate, YourSUMquery.SumOfShpQty
FROM Shipments INNER JOIN Jobs ON YourSUMquery.Job_ID = Jobs.JOBNO
GROUP BY Jobs.JOBNO, Jobs.QTY, Shipments.DueDate, Shipments.ShipDate, Shipments.ShipQty
YourSUMquery.SumOfShpQty
HAVING (((Jobs.JOBNO)=42348));

HTH,
Daniel
0
 
Michaelj42Author Commented:
Perfect, I was suspecting it had to be something of that order but wasn't finding the exact process.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now