• ** There has been a recent site upgrade. Please clear your browser cache to avoid issues. **
  • New forum feature - post voting and best solution

    Check out this thread for the details: https://www.access-programmers.co.uk/forums/threads/new-forum-feature-post-voting-and-best-answer.314134/

    This new feature looks great to me! :)

  • We now have 3 forum themes

    Go for the default (light) theme, Shades of Grey or Shades of Blue. I just added the Blue one.

    The thread about it is here: https://www.access-programmers.co.uk/forums/threads/new-forum-theme-shades-of-blue.314136/

Solved Query SUM not summing (1 Viewer)

ChrisC

Registered User.
Local time
Today, 18:03
Joined
Aug 13, 2019
Messages
84
Hello everyone,

I have the following query:

SELECT tblPurchaseOrders.PartNumber, Sum(tblPurchaseOrders.OrderedQty) AS SumOfOrderedQty, tblPurchaseOrders.OrderDate
FROM tblPurchaseOrders
GROUP BY tblPurchaseOrders.PartNumber, tblPurchaseOrders.OrderDate
HAVING (((tblPurchaseOrders.OrderDate)>=DateAdd("m",-6,Date())));

There are multiple "OrderedQty" entries in tblPurchaseOrders for each PartNumber; each with a different order date.
I want to display the total OrderedQty for each PartNumber where the date given in tblPurchaseOrders for the entry is less than 6 months old from "today".

The SUM function works perfectly as it should until I add the 6 month criteria on the date field; at which point the query returns only the 6 month orders but no longer sum's them - instead it shows each entry.

For example, PartNumber S14141 was ordered several times since the start of time, totalling 850 units. Here is the query without the date criteria:

1600178911765.png


So far, so good. But...

During the last 6 months, the part has been ordered twice - once for 50 units and once for 200. I want the query to therefore show for part S14141 the SumOfOrderedQty = 250. Instead it itemises the 6 monthly transactions:

1600179082020.png


I'm sure its a simple thing to correct but as usual, I am blind to it!

Any thoughts gratefully received as always!

Chris
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 14:03
Joined
May 21, 2018
Messages
3,493
You cannot include the purchase order date in the group by. Each is unique
 

ChrisC

Registered User.
Local time
Today, 18:03
Joined
Aug 13, 2019
Messages
84
ah i see, thank you - which heading would this come under instead? I have tried simply deleting it from the Group By section but of course that wont work on its own - i get the error:
1600180567140.png
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 14:03
Joined
May 21, 2018
Messages
3,493
May need a calculated field that returns true or false if < 6 months. Then you can include that grouping.

Maybe something like (not sure I got all the () correct)
iif( tblPurchaseOrders.OrderDate >=DateAdd("m",-6,Date()), True,False) as Six_Mos
and group on that

To make it a little easier I would probably build the calculated field in a separate query and filter out the records you do not need. Then use that query in the aggregate.
 

namliam

The Mailman - AWF VIP
Local time
Today, 19:03
Joined
Aug 11, 2003
Messages
11,667
Or simply alter your SQL :
Code:
SELECT tblPurchaseOrders.PartNumber, Sum(tblPurchaseOrders.OrderedQty) AS SumOfOrderedQty
FROM tblPurchaseOrders
GROUP BY tblPurchaseOrders.PartNumber
HAVING (((tblPurchaseOrders.OrderDate)>=DateAdd("m",-6,Date())));
 

ChrisC

Registered User.
Local time
Today, 18:03
Joined
Aug 13, 2019
Messages
84
Hi everyone - thanks for the suggestions and help :)

CJ_London - that worked perfectly! I knew it would be something simple!

thanks again
Chris
 

Attachments

  • 1600242033895.png
    1600242033895.png
    9.7 KB · Views: 13

vhung

Member
Local time
Today, 11:03
Joined
Jul 8, 2020
Messages
144
Hello everyone,
During the last 6 months, the part has been ordered twice - once for 50 units and once for 200. I want the query to therefore show for part S14141 the SumOfOrderedQty = 250. Instead it itemises the 6 monthly transactions:
hi nice query run
>try the sql of query below
SELECT Table1.PartNumber, Sum(Table1.SumOforderedqty) AS SumOfSumOforderedqty, First(Table1.orderdate) AS FirstOforderdate, Table1.PartNumber, Last(Table1.orderdate) AS LastOforderdate, First(Table1.SumOforderedqty) AS FirstOfSumOforderedqty, Last(Table1.SumOforderedqty) AS LastOfSumOforderedqty
FROM Table1
GROUP BY Table1.PartNumber;
>see attachment
 

Attachments

  • querygroup.png
    querygroup.png
    359.7 KB · Views: 13
Last edited:

Users who are viewing this thread

Top Bottom