Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Queries _ Sum Based On Criteria

Posted by: ThankYou1 Dec 6 2017, 04:44 PM

See attached. I need to write a query that has a total for "Unique PO Count" and "Lines" if the Method field indicates anything other than EDX. So ideally, I'd have 2 records for each day one with totals for EDX and with with totals for all others - where all others are combined. 7/2 for example would be for EDX unique po count = 27, Lines = 227, and Amt = 7,437 and then Other Unique PO count = 42, Lines = 2268, and Amt = 47,384. Hope that's enough info. Thanks.

 sample.pdf ( 100.02K ): 16
 

Posted by: ADezii Dec 6 2017, 07:38 PM

I am not sure how to arrive at a purely SQL-based solution, but a Code-based solution should not be that difficult. Let me see what happens and if a more efficient SQL solution does not come along, I'll Post what I have. I am assuming that you want the Results to be displayed as:

CODE
PO Date       Method        PO Count      Lines         Amt
--------------------------------------------------------------------
7/2/2017      EDX            27            227          7,437
7/2/2017      Other          42            2268         47,384
--------------------------------------------------------------------
7/3/2017      EDX            164           563          58,913
7/3/2017      Other          193           602          428,660
--------------------------------------------------------------------
7/4/2017      EDX            23            150          4,808
7/4/2017      Other          40            1549         24,134
--------------------------------------------------------------------

Posted by: ThankYou1 Dec 6 2017, 08:03 PM

Yes. That display would be good. And a total number combining EDX and Other would be good too. I was able to use Access to get 3 queries - one for each(EDX, other, and total) but now need to combine them and donít know how to do that without a good deal of manual work.

Posted by: HairyBob Dec 7 2017, 04:45 AM

You can do this by unioning (if that's the right word) the three queries together - see attached file.

The query is based on a table with the first four lines of your data in it (i.e. for 7/2/2017) and I'm assuming from looking at your data, that there can only be one record for a certain method in a given date group.

 SumBasedOnCriteria.zip ( 71.51K ): 6
 

Posted by: ThankYou1 Dec 7 2017, 08:03 AM

Thank you!

Posted by: HairyBob Dec 7 2017, 09:53 AM

yw.gif

Posted by: ADezii Dec 7 2017, 11:33 AM

First of all, Hairy Bob's approach would be a better and probably more efficient option. If you still wish to see a purely Code-Based solution with the Output going into a Table and a third Row added for each PO Date displaying the PO Date, ALL in the Method Field, Total PO Count, Total Lines, and Total Amt, just let me know and I can finalize it tomorrow.

@HairyBob:
How would you implement the Totals into your UNION Queries?

Posted by: NimishParikh Dec 8 2017, 02:49 AM

Can't you create another table as follows to include helper field (INCLD) in the query and then run new query to include this newly conceived filed in GROUP BY clause?

CODE
New table
=========
METHOD    INCLD
EDX       EDX
EML       OTHER
FAX       OTHER
PRN       OTHER


Nimish

Posted by: HairyBob Dec 8 2017, 05:24 AM

Hi ADezii,

The Totals in my query is the last in the union:

CODE
SELECT [PO Date], "EDX" AS Method, [Unique PO Count], Lines, Amt FROM tblSummaryData WHERE Method = "EDX"
UNION ALL
SELECT [PO Date], "Other" AS Method, Sum([Unique PO Count]), Sum(Lines), Sum(Amt) FROM tblSummaryData WHERE Method <> "EDX" GROUP BY [PO Date]
UNION ALL
SELECT [PO Date], Null, Sum([Unique PO Count]), Sum(Lines), Sum(Amt) FROM tblSummaryData GROUP BY [PO Date];


I indicated the totals row with a Null in the 'Method' column however, you could replace this with a string if you want (e.g. "ALL").

If you want the result set from this query inserted into a table using VBA, you could use the union SQL as a derived table in an INSERT statement (see attachment):

CODE
INSERT INTO tblAggSummaryData ( [PO Date], Method, [Unique PO Count], Lines, Amt )
SELECT [PO Date], Method, [Unique PO Count], Lines, Amt
FROM
(SELECT [PO Date], "EDX" AS Method, [Unique PO Count], Lines, Amt FROM tblSummaryData WHERE Method = "EDX"
UNION ALL
SELECT [PO Date], "Other" AS Method, Sum([Unique PO Count]), Sum(Lines), Sum(Amt) FROM tblSummaryData WHERE Method <> "EDX" GROUP BY [PO Date]
UNION ALL
SELECT [PO Date], Null, Sum([Unique PO Count]), Sum(Lines), Sum(Amt) FROM tblSummaryData GROUP BY [PO Date])  AS DRV;


If you wanted to do the insert in VBA, you could build the above INSERT statement in an SQL string and execute it (using CurrentDB.Execute).

NOTE: If you want to maintain a primary key in the destination table (tblAggsummaryData in my example) comprising the date and method, you would have to use a string in the 'Method' column for the totals row as the 'Method' field cannot allow nulls if it's part of a primary key.

Regards,

Hairy.

 SumBasedOnCriteria2.zip ( 77.8K ): 0
 

Posted by: ADezii Dec 8 2017, 08:04 AM

Nice approach, HairyBob.

Posted by: HairyBob Dec 8 2017, 10:20 AM

thanks.gif