UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Sum Based On Criteria, Access 2007    
 
   
ThankYou1
post Dec 6 2017, 04:44 PM
Post#1



Posts: 25
Joined: 19-August 10



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.
Attached File(s)
Attached File  sample.pdf ( 100.02K )Number of downloads: 16
 
Go to the top of the page
 
ADezii
post Dec 6 2017, 07:38 PM
Post#2



Posts: 1,857
Joined: 4-February 07
From: USA, Florida, Delray Beach


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
--------------------------------------------------------------------
Go to the top of the page
 
ThankYou1
post Dec 6 2017, 08:03 PM
Post#3



Posts: 25
Joined: 19-August 10



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.
Go to the top of the page
 
HairyBob
post Dec 7 2017, 04:45 AM
Post#4



Posts: 875
Joined: 26-March 08
From: London


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.
Attached File(s)
Attached File  SumBasedOnCriteria.zip ( 71.51K )Number of downloads: 5
 

--------------------
If it was easy, It wouldn't be fun!
Go to the top of the page
 
ThankYou1
post Dec 7 2017, 08:03 AM
Post#5



Posts: 25
Joined: 19-August 10



Thank you!
Go to the top of the page
 
HairyBob
post Dec 7 2017, 09:53 AM
Post#6



Posts: 875
Joined: 26-March 08
From: London


yw.gif

--------------------
If it was easy, It wouldn't be fun!
Go to the top of the page
 
ADezii
post Dec 7 2017, 11:33 AM
Post#7



Posts: 1,857
Joined: 4-February 07
From: USA, Florida, Delray Beach


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?
Go to the top of the page
 
NimishParikh
post Dec 8 2017, 02:49 AM
Post#8



Posts: 198
Joined: 30-November 10



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
This post has been edited by NimishParikh: Dec 8 2017, 02:51 AM
Go to the top of the page
 
HairyBob
post Dec 8 2017, 05:24 AM
Post#9



Posts: 875
Joined: 26-March 08
From: London


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.
This post has been edited by HairyBob: Dec 8 2017, 05:28 AM
Attached File(s)
Attached File  SumBasedOnCriteria2.zip ( 77.8K )Number of downloads: 0
 

--------------------
If it was easy, It wouldn't be fun!
Go to the top of the page
 
ADezii
post Dec 8 2017, 08:04 AM
Post#10



Posts: 1,857
Joined: 4-February 07
From: USA, Florida, Delray Beach


Nice approach, HairyBob.
Go to the top of the page
 
HairyBob
post Dec 8 2017, 10:20 AM
Post#11



Posts: 875
Joined: 26-March 08
From: London


thanks.gif

--------------------
If it was easy, It wouldn't be fun!
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    11th December 2017 - 02:09 PM