• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 214
  • 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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

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