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

BeardedSith

Member
Local time
Today, 01:51
Joined
Feb 5, 2020
Messages
51
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


IDMemberIDPurchaseDatePurchaseAmount
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
IDCustomerIDIssueDateIssueAmount
6008263472/6/2020$100.00
600712/4/2020$100.00
60062634712/29/2019$100.00

CURRENT OUTPUT
qryCalculations

MemberIDNumPurchasesSumOfPurchaseAmountRealDateIsEligibleEligibleAmount
17$1915.622/4/20201$100.00
263475$1236.252/6/20200$100.00

EXPECTED RESULTS:
qryCalculations


MemberIDNumPurchasesSumOfPurchaseAmountRealDateIsEligibleEligibleAmount
13$1,915.622/4/20200$0.00
263470$0.002/6/20200$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.
  1. Why isn't MemberID=2 shown in the results? Because no purchases were made in the current Calendar year.
  2. What is RealDate and IsEligible? You can ignore these, they're expressions in the query.
  3. What is EligibleAmount? Another expression, ignore it.
 
Last edited:

plog

Banishment Pending
Local time
Today, 00:51
Joined
May 11, 2011
Messages
9,642
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

Member
Local time
Today, 01:51
Joined
Feb 5, 2020
Messages
51
@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

Banishment Pending
Local time
Today, 00:51
Joined
May 11, 2011
Messages
9,642
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

Member
Local time
Today, 01:51
Joined
Feb 5, 2020
Messages
51
@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



CustomerIDRealDate
26347​
2/6/2020​
1​
2/4/2020​
25012​
11/23/2019​

sqryCalc2 - Works fine


MemberIDPurchaseDateDailyPurchaseTotal
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.

MemberIDNumPurchasesSumOfPurchaseAmountRealDate
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

MemberIDNumPurchasesSumOfPurchaseAmountRealDate
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.
 
Last edited:

plog

Banishment Pending
Local time
Today, 00:51
Joined
May 11, 2011
Messages
9,642
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

Member
Local time
Today, 01:51
Joined
Feb 5, 2020
Messages
51
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

MemberIDDatePurchasePurchaseAmount
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

CustomerIDRealDate
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

MemberIDDatePurchaseDailyPurchaseTotal
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

CustomerIDNumPurchasesSumOfPurchaseAmountRealDate
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

CustomerIDRealAmountRealDateIsEligible
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));
 
Last edited:

plog

Banishment Pending
Local time
Today, 00:51
Joined
May 11, 2011
Messages
9,642
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:


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

Member
Local time
Today, 01:51
Joined
Feb 5, 2020
Messages
51
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!
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom