Using Count() and Last() in the same query

BeardedSith · Feb 17, 2020

I'm trying very hard here to provide the requested information. If this is missing, please be specific so I can provide it. Also, I would LOVE this to all be one query (all the queries up to this point) but I can sort this all out after I figure out the Count(*) problem below.

I need to count the # of unique dates in tbPurchases between the greater of ( Max(tblRewards.IssueDate) or DateSerial(Year(Now()),1,1,) ) and Now(). Records previous to these dates should not be counted. Here is some example data:

tbPurchases

ID	MemberID	PurchaseDate	PurchaseAmount

5638	1	2/13/2020	$500.00
5637	1	2/13/2020	$1,000.00
5636	1	2/13/2020	$100.00
5635	1	2/11/2020	$79.99
5634	1	2/6/2020	$85.63
5633	1	2/6/2020	$150.00
5631	26347	2/6/2020	$586.25
5630	26347	2/6/2020	$150.00
5629	26347	2/6/2020	$50.00
5628	26347	2/6/2020	$300.00
5627	26347	2/6/2020	$150.00
5626	1	2/3/2020	$15.00
5622	1	1/24/2020	$65.00
5621	1	1/24/2020	$2,500.00
5620	1	1/24/2020	$15.00
5624	1	1/24/2020	$55.00
5625	1	1/24/2020	$95.00
5623	1	1/24/2020	$35.00
1	1	1/9/2020	$150.99
3191	26347	12/3/2019	$4.17
4575	26347	12/3/2019	$4.99
2886	26347	12/3/2019	$101.95
346	26347	12/3/2019	$551.18
4846	26347	12/3/2019	$1,857.70
1232	26347	11/30/2019	$1.50
3485	26347	11/29/2019	$130.98
3484	2	11/20/2019	$300.00
3483	2	11/20/2019	$300.00
3482	2	11/20/2019	$300.00
3481	2	11/20/2019	$300.00

tblRewards

ID	CustomerID	IssueDate	IssueAmount
6008	26347	2/6/2020	$100.00
6007	1	2/4/2020	$100.00
6006	26347	12/29/2019	$100.00

CURRENT OUTPUT
qryCalculations

MemberID	NumPurchases	SumOfPurchaseAmount	RealDate	IsEligible	EligibleAmount
1	7	$1915.62	2/4/2020	1	$100.00
26347	5	$1236.25	2/6/2020	0	$100.00

EXPECTED RESULTS:
qryCalculations

MemberID	NumPurchases	SumOfPurchaseAmount	RealDate	IsEligible	EligibleAmount
1	3	$1,915.62	2/4/2020	0	$0.00
26347	0	$0.00	2/6/2020	0	$0.00

ISSUES:

NumPurchases - Currently it's counting each purchase as an individual Count(*)
- I need it to count unique dates the purchases were made
- Example: 2/13/2020 - MemberID=1 made 3 purchases. These should only be counted as 1.
SumOfPurchaseAmount - Currently calculating all purchases, and not those from NumPurchases

CURRENT SQL:
For a starting point, here is the current SQL statement:

Code:

SELECT qryCountCurrentYear.MemberID, qryCountCurrentYear.NumPurchases, qryCountCurrentYear.SumOfPurchaseAmount, sqryLastRewards.RealDate, IIf([qryCountCurrentYear]![NumPurchases]>=[tblOptions]![NumPurchases],1,0) AS IsEligible, IIf([qryCountCurrentYear]![SumOfPurchaseAmount]>[tblOptions]![MaxIssue],[tblOptions]![MaxIssue],[qryCountCurrentYear]![SumOfPurchaseAmount]) AS EligibleAmount
FROM tblOptions, qryCountCurrentYear INNER JOIN sqryLastRewards ON qryCountCurrentYear.MemberID = sqryLastRewards.CustomerID;

There may be questions, so if so, check here. I'll answer questions here, too.

Why isn't MemberID=2 shown in the results? Because no purchases were made in the current Calendar year.
What is RealDate and IsEligible? You can ignore these, they're expressions in the query.
What is EligibleAmount? Another expression, ignore it.

plog · Feb 17, 2020

I don't think your expectations and data jive for NumPurchases/SumOfPurchaseAmount. I'm pretty sure its just a typo/adding error in the expectations, but I will write in a tone assuming the expectations are correct and I am just missing something in the logic (no condescension or patronization is meant).

SumOfPurchaseAmountNum:
MemberID=1 -> In tbPurchases there are 7 records after 2/4/2020 totaling 2015.62, you expect 1915.62 --> Is ID=5639 or ID=5636 not included and why?

NumOfOPurchaseAmount:
MemberID=1 -> In tbPurchases those 7 records after 2/4/2020 have 4 unique dates after 2/4/2020, you expect 3 --> whats the ID of the record not to be included and why?

BeardedSith · Feb 17, 2020

@plog - I forgot I added a new record to the mix when I was playing with the data earlier last week. When I copy/pasted the data over, it messed up the math...again.

The record for 2/14/2020 was left out in the math. Sorry. I'll remove it from the example data to clear up the mistake. The reason for the mistake is I was copy/pasting some of the example data from earlier posts, and the rest from the database. That's my fault.

plog · Feb 17, 2020

This is going to take 3 total queries:

Code:

SELECT tblRewards.CustomerID, Max(tblRewards.IssueDate) AS RealDate
FROM tblRewards
GROUP BY tblRewards.CustomerID;

Paste that SQL in and name the query "MainQuery_sub1". It simply gets the latest reward date for all CustomerIDs in tblRewards.

Code:

SELECT tbPurchases.MemberID, tbPurchases.PurchaseDate, Sum(tbPurchases.PurchaseAmount) AS DailyPurchaseTotal
FROM tbPurchases INNER JOIN MainQuery_sub1 ON tbPurchases.MemberID = MainQuery_sub1.CustomerID
WHERE (((tbPurchases.PurchaseDate)>[RealDate] And (tbPurchases.PurchaseDate)>CDate("1/1/" & Year(Date()))))
GROUP BY tbPurchases.MemberID, tbPurchases.PurchaseDate;

Paste that SQL in and name it "MainQuery_sub2" . It totals the PurchaseAmounts for each MemberID per unique day and filters out purchases we don't count based on their date.

Code:

SELECT MainQuery_sub1.CustomerID AS MemberID, Count(MainQuery_sub2.MemberID) AS NumPurchases, Sum(Nz([DailyPurchaseTotal],0)) AS SumOfPurchaseAmount, MainQuery_sub1.RealDate
FROM MainQuery_sub1 LEFT JOIN MainQuery_sub2 ON MainQuery_sub1.CustomerID = MainQuery_sub2.MemberID
GROUP BY MainQuery_sub1.CustomerID, MainQuery_sub1.RealDate;

Paste that and run it and you will get the results you expect when you feed it the data you provided me.

And as usual, when you run this on a different data set and it doesn't produce the results you expect, provide me the data you are feeding it (A) and the results you expect (B) for that data.

BeardedSith · Feb 18, 2020

@plog - What you posted is really close to what I was trying to do myself. The issue here is it's still counting each tbPurchases.PurchaseDate as an individual purchase, and not counting each distinct PurchaseDate as a single item.

Here's the output I'm getting, using your three queries, based on the data above (I renamed the queries from your suggested names to fit my naming schema):

sqryCalc1 - Works fine

CustomerID	RealDate
26347	2/6/2020
1	2/4/2020
25012	11/23/2019

sqryCalc2 - Works fine

MemberID	PurchaseDate	DailyPurchaseTotal
1	2/6/2020	$150.00
1	2/6/2020	$85.63
1	2/11/2020	$79.99
1	2/13/2020	$100.00
1	2/13/2020	$1,000.00
1	2/13/2020	$500.00
26347	2/6/2020	$150.00
26347	2/6/2020	$300.00
26347	2/6/2020	$50.00
26347	2/6/2020	$150.00
26347	2/6/2020	$586.25

sqryCalc3 - Is counting purchases that happened on the same date as multiple purchases. Each PurchaseDate above should only be counted one time.

MemberID	NumPurchases	SumOfPurchaseAmount	RealDate
1	6	1915.62	2/4/2020
26347	5	1236.25	2/6/2020

EXPECTED OUTPUT:
Explanation -

NumPurchases - For MemberID=1, there were 6 purchases, but those 6 purchases occurred on 3 different dates. So it should only count them 3 times.
SumOfPurchaseAmount - For both MemberID's, they don't meet the threshold for NumPurchases. This column doesn't have to be 0.00, but for MemberID=2, it should definitely be 0.00, because the NumPurchases=0

MemberID	NumPurchases	SumOfPurchaseAmount	RealDate
1	3	0.00	2/4/2020
26347	0	0.00	2/6/2020

Is it possible that the formatting of my data for tbPurchases.PurchaseDate could be the issue? I'm working with really old data (10+ year old Access Database that I'm re-writing), and one thing I've noticed elsewhere is how dates are saved into each table. I've tried to reset the dates where I've noticed issues popping up, but is it possible that even though I have it set to Short Date, could the fact that different records are put into the database at different times be the issue?

Finally, is there a way at all to put these into one, single query? I know Access is fickle when it comes to nested queries, so it might be an issue.

plog · Feb 18, 2020

We can nest the queries later, let's leave them apart for now so that we can debug this thing easier. With this issue we can focus on getting sqryCalc2 to work because its not working as expected. There should only be 1 record per MemberID/PurchaseDate.

I think PurchaseDate might contain the Time as well as the date, is that true? The solution to that is to first use DateVAlue() on PurchaseDate to get just the date in PurchaseDate:

Code:

SELECT tbPurchases.MemberID, DateValue([PurchaseDate]) AS DatePurchase, tbPurchases.PurchaseAmount
FROM tbPurchases;

Using my naming system you would name it 'MainQuery_sub0' and then in the other 3 queries I wrote; replace tbPurchases with it.

If thats too much to do on your own, can you get me a copy of the database in which you pasted my SQL for this? That way I can verify this is the actual cause and write the solution.

BeardedSith · Feb 19, 2020

Holy crap @plog, you're on to something here! It's pretty obvious now that the date was being stored with full time included.

This might be a wall of SQL text/etc., so bare with me, please. Most of this is for reference purposes and not something you need to delve into.

I created a new query called sqryCalc0 which grabs and formats data from tbPurchases. Using your SQL above:

Code:

SELECT tbPurchases.MemberID, DateValue([PurchaseDate]) AS DatePurchase, tbPurchases.PurchaseAmount
FROM tbPurchases;

This outputs:
sqryCalc0

MemberID	DatePurchase	PurchaseAmount
1	2/13/2020	$1,000.00
1	2/13/2020	$100.00
1	2/13/2020	$500.00
1	2/11/2020	$79.99
26347	2/6/2020	$150.00
1	2/6/2020	$85.63
1	2/6/2020	$150.00
26347	2/6/2020	$586.25
26347	2/6/2020	$150.00
26347	2/6/2020	$50.00
26347	2/6/2020	$300.00
1	2/3/2020	$15.00
1	1/24/2020	$55.00
1	1/24/2020	$35.00
1	1/24/2020	$65.00
1	1/24/2020	$2,500.00
1	1/24/2020	$15.00
1	1/24/2020	$95.00
1	1/9/2020	$150.99
26347	12/3/2019	$551.18
26347	12/3/2019	$101.95
26347	12/3/2019	$4.17
26347	12/3/2019	$1,857.70
26347	12/3/2019	$4.99
26347	11/30/2019	$1.50
26347	11/29/2019	$130.98

So far, great!

Then, using your queries from a previous post, I have this for sqryCalc1:

Code:

SELECT tblRewards.CustomerID, IIf(Max([tblRewards]![IssueDate]<DateSerial(Year(Now()),1,1)),DateSerial(Year(Now()),1,1),Max([tblRewards]![IssueDate])) AS RealDate
FROM tblRewards
GROUP BY tblRewards.CustomerID;

Which outputs:
sqryCalc1

CustomerID	RealDate
26347	2/6/2020
1	2/4/2020
26403	1/1/2020

NOTE: I included MemberID=26403 to demonstrate the logical test in the SQL statement above.

Great again!

Next, we have sqryCalc2:

Code:

SELECT sqryCalc0.MemberID, sqryCalc0.DatePurchase, Sum(sqryCalc0.PurchaseAmount) AS DailyPurchaseTotal
FROM sqryCalc0 INNER JOIN sqryCalc1 ON sqryCalc0.MemberID = sqryCalc1.CustomerID
WHERE (((sqryCalc0.DatePurchase)>[RealDate] And (sqryCalc0.DatePurchase)>CDate("1/1/" & Year(Date()))))
GROUP BY sqryCalc0.MemberID, sqryCalc0.DatePurchase;

Which outputs:
sqryCalc2

MemberID	DatePurchase	DailyPurchaseTotal
1	2/6/2020	$235.63
1	2/11/2020	$79.99
1	2/13/2020	$1,600.00

Which is PERFECT! These are the only three purchases that should be tallied, so you're rocking so far!

Finally, we have sqryCalc3:

Code:

SELECT sqryCalc1.CustomerID, Count(sqryCalc2.MemberID) AS NumPurchases, Sum(Nz([DailyPurchaseTotal],0)) AS SumOfPurchaseAmount, sqryCalc1.RealDate
FROM sqryCalc1 LEFT JOIN sqryCalc2 ON sqryCalc1.CustomerID = sqryCalc2.MemberID
GROUP BY sqryCalc1.CustomerID, sqryCalc1.RealDate;

Which outputs:
sqryCalc3

CustomerID	NumPurchases	SumOfPurchaseAmount	RealDate
26347	0	0	2/6/2020
1	3	1915.62	2/4/2020

BINGO!

@plog - You have helped me in ways you probably couldn't appreciate, so I thank you profusely and I'm glad we worked through our communicative issues to get this done. The only thing at this point, is whether or not we could nest these together? This is nothing more than a preference so I can operate all this math "behind the scenes". Ideally (and I can do this myself) I'd like to create a procedure which performs all these calculations each time the end-user operates a control on the form. Nesting these queries together into some singular "line of code" I could call regularly.

EDIT:
Just for completions sake (and in case this could help someone in the future), I added a fifth query that does most of the heavy lifting in terms of logical checks:

Code:

SELECT sqryCalc3.CustomerID, IIf([sqryCalc3]![SumOfPurchaseAmount]>[tblOptions]![MaxIssue],[tblOptions]![MaxIssue],[sqryCalc3]![SumOfPurchaseAmount]) AS RealAmount, sqryCalc3.RealDate, IIf([sqryCalc3]![NumPurchases]>[tblOptions]![NumPurchases],1,0) AS IsEligible
FROM tblOptions, sqryCalc3
WHERE (((IIf([sqryCalc3]![NumPurchases]>[tblOptions]![NumPurchases],1,0))=1));

Which outputs:
qryEligibility

CustomerID	RealAmount	RealDate	IsEligible
1	$100.00	2/4/2020	1

If I could get this query incorporated into the "single-query" I'm hoping to accomplish with nesting them together, that would be amazing.

And, just to help anyone later on down the road with a similar issue, here's my Append query:

Code:

INSERT INTO tblRewards ( CustomerID, IssueDate, IssueAmount )
SELECT qryEligibility.CustomerID, DateSerial(Year(Now()),Month(Now()),Day(Now())) AS Today, qryEligibility.RealAmount
FROM qryEligibility
WHERE (((qryEligibility.IsEligible)=1));

plog · Feb 19, 2020

I'm heading out of town and won't be able to put all those subqueries together, but I can help you do it. Here's a link:

SQL Subqueries - Microsoft Support

support.office.com

It's like algebraic replacement:

2x + y = 7
y=4-x

To solve for x in the first equation you replace y with what its equivalent to in the second:

2x + (4-x) = 7

That's essentially the same method for getting all your SQL into one query. Start with the last query in the process and then replace the name of a subquery with its actual SQL. In some cases you will have to use an alias (see the link).

If you are still stumped, start a new thread and someone can walk you thru it.

BeardedSith · Feb 19, 2020

Yeah, this is something I might need help with. Nesting all these SQL queries into one is a bit more daunting than I thought. I got confused two minutes into it. I'll post another thread. Thanks again, @plog for all the help, and I hope you enjoy your time out of town!

Using Count() and Last() in the same query

BeardedSith

Member

plog

Banishment Pending

BeardedSith

Member

plog

Banishment Pending

BeardedSith

Member

plog

Banishment Pending

BeardedSith

Member

plog

Banishment Pending

SQL Subqueries - Microsoft Support

BeardedSith

Member

Similar threads

Users who are viewing this thread