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

plog

Banishment Pending
Local time
Today, 06:44
Joined
May 11, 2011
Messages
11,611
Try this for the FROM and INNER JOIN lines:

FROM tbPurchases INNER JOIN tblRewards ON tbPurchases.MemberID = tblRewards.CustomerID
 

BeardedSith

Member
Local time
Today, 07:44
Joined
Feb 5, 2020
Messages
73
Using this I get an operator error. I'm not the best at SQL, and it's showing.
Code:
SELECT MemberID, SUM(PurchaseAmount) AS SumOfPurchaseAmount, COUNT(MemberID) AS CountOfIssueDate
FROM tbPurchases INNER JOIN tblRewards ON tbPurchases.MemberID = tblRewards.CustomerID
INNER JOIN tblRewards ON CustomerID=MemberID
WHERE PurchaseDate>IssueDate
GROUP BY MemberID
 

plog

Banishment Pending
Local time
Today, 06:44
Joined
May 11, 2011
Messages
11,611
I downloaded your database and was able to make it work in there. This is the SQL that works in your uploaded backend file:

Code:
SELECT tbPurchases.MemberID, Sum(tbPurchases.PurchaseAmount) AS SumOfPurchaseAmount, Count(tbPurchases.MemberID) AS CountOfIssueDate
FROM tbPurchases INNER JOIN tblRewards ON tbPurchases.MemberID = tblRewards.CustomerID
WHERE (((tbPurchases.[PurchaseDate])>[IssueDate]))
GROUP BY tbPurchases.MemberID;
 

BeardedSith

Member
Local time
Today, 07:44
Joined
Feb 5, 2020
Messages
73
Alright! But that presents another problem. With data that exists before the current year, ALL purchases are being counted. We reset all rewards every year.

Using this SQL I get all the information necessary, just not sure how to make it work. The data given in the shared backend file should only show two customers, because they're the only ones with purchases > 1/1/2020.

Code:
SELECT tbPurchases.MemberID, Sum(tbPurchases.PurchaseAmount) AS SumOfPurchaseAmount, Count(tbPurchases.MemberID) AS CountOfIssueDate, Max(tbPurchases.PurchaseDate) AS MaxOfPurchaseDate
FROM tbPurchases INNER JOIN tblRewards ON tbPurchases.MemberID = tblRewards.CustomerID
WHERE (((tbPurchases.PurchaseDate)>[IssueDate]))
GROUP BY tbPurchases.MemberID;
 

plog

Banishment Pending
Local time
Today, 06:44
Joined
May 11, 2011
Messages
11,611
A&B data please:

A. Starting data from your tables. Include table and field names and enough sample data to cover all cases.

B. Expected results if A. show me the data you expect the query to produce when you feed it the data from A.

Demonstrate the issue with data.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:44
Joined
Feb 28, 2001
Messages
26,996
In the relationships earlier in your thread, you have no relationships to MemberID. Does it have an index? Because to do an INNER JOIN both fields have to be indexed. (Or last I remember, they did.)

Probably wouldn't HURT to be pedantic and qualify the JOIN fields with their table names, e.g. tbPurchases.CustomerID - might work without it, won't hurt to try it.
 

BeardedSith

Member
Local time
Today, 07:44
Joined
Feb 5, 2020
Messages
73
In the relationships earlier in your thread, you have no relationships to MemberID. Does it have an index? Because to do an INNER JOIN both fields have to be indexed. (Or last I remember, they did.)

Probably wouldn't HURT to be pedantic and qualify the JOIN fields with their table names, e.g. tbPurchases.CustomerID - might work without it, won't hurt to try it.
It's there.
 

BeardedSith

Member
Local time
Today, 07:44
Joined
Feb 5, 2020
Messages
73
tblRewards:
IDCustomerIDIssueDateIssueAmount
112/2/2020$100.00
222/3/2020$46.63

tbPurchases:
IDMemberIDPurchaseDatePurchaseAmount
1111/12/2019$15.00
2111/13/2019$1500.00
3112/12/2019$1000.00
411/22/2020$5.00
511/29/2020$100.00
612/3/2020$100.00
722/7/2020$100.00
822/7/2020$100.00
922/8/2020$100.00
1022/9/2020$100.00
1112/10/2020$100.00
1222/10/2020$100.00
1322/10/2020$5.00
1412/12/2020$100.00
1512/13/2020$100.00

EXPECTED OUTPUT pt 2
MemberIDSumOfPurchaseAmountCountOfIssueDate
1$400.004
2$505.006

All this example data is wonky to deal with, so I just took a small image to show what I'm talking about.
Capture.PNG

In this image:
- MemberID #1 should be the only thing showing. You won't know by this image, but all over purchases from tbPurchases are prior to the current calendar year.
- The MemberID's that ARE showing are counting all purchases since the creation of the program, and not those > tblRewards.IssueDate AND > DateSerial(Year(Now()),1,1)
 
Last edited:

plog

Banishment Pending
Local time
Today, 06:44
Joined
May 11, 2011
Messages
11,611
You really are doing your best to confuse things. A & B data, I don't know what that screenshot is for, so I will be addressing the sample data and expected out pt2 that you posted:

My query will produce your expected results for MemberID=2. My query will produce SumOfPurchaseAMount=400 and CountOfIssueDate=4 for MemberID=1. Can you explain why MemberID=1 would have 505 for SumOfPurchaseAmount in the data you just provided?
 

BeardedSith

Member
Local time
Today, 07:44
Joined
Feb 5, 2020
Messages
73
You really are doing your best to confuse things. A & B data, I don't know what that screenshot is for, so I will be addressing the sample data and expected out pt2 that you posted:

My query will produce your expected results for MemberID=2. My query will produce SumOfPurchaseAMount=400 and CountOfIssueDate=4 for MemberID=1. Can you explain why MemberID=1 would have 505 for SumOfPurchaseAmount in the data you just provided?
MemberID=1 only has 400(actually 400, not 505 - read below) after Max(tblRewards.IssueDate) and DateSerial(Year(Now()),1,1). I also noticed my math is wrong (again...sigh). Fixing the EXPECTED OUTPUT Pt2 values. MemberID=1 should have a SumOfPurchaseAmount of $400.00. There were only 4 purchases after tblRewards.IssueDate. All these numbers are seriously making my brain spin in circles lol. Sorry!
 

plog

Banishment Pending
Local time
Today, 06:44
Joined
May 11, 2011
Messages
11,611
My query produces both those results. Problem solved.
 

BeardedSith

Member
Local time
Today, 07:44
Joined
Feb 5, 2020
Messages
73
Does it produce those proper results in the backend1 I shared? I'm getting a count of ALL purchases and a sum of ALL purchases in the database. I need them filtered. I need the query to return only those results where the PurchaseDate is > Max(Max(IssueDate) OR DateSerial(Year(Now()),1,1)))

That's how I imagine it via the built-in Expression Builder at least.
 

plog

Banishment Pending
Local time
Today, 06:44
Joined
May 11, 2011
Messages
11,611
It produces the expected results in your post #28 which is the sample data (remember all those times I said "A & B" data?) we were working with.

If it is not producing the expected results on a different set of data then you must give me the input data (A) and the expected results data (B) for that data set.
 

BeardedSith

Member
Local time
Today, 07:44
Joined
Feb 5, 2020
Messages
73
@plog - I'm curious, why does the data set make this easier on you to create the SQL statement? Giving the data set is a whole lot more information than is actually needed to change Count(*) to Count(*) different dates instead of all the records found. Just trying to get inside your mind and understand your process. Maybe it'll help me learn something. :love:

Either way, here's your A&B (This is the exact data from the backend1 I shared):

sqryLastRewards:
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;


CustomerIDRealDate
1​
2/4/2020​
26347​
2/6/2020​
24557​
1/1/2020​
24560​
1/1/2020​
24561​
1/1/2020​
24562​
1/1/2020​
24563​
1/1/2020​
24566​
1/1/2020​
24569​
1/1/2020​

qryCountCurrentYear
Code:
SELECT tbPurchases.MemberID, Count(*) AS NumPurchases, Sum(tbPurchases.PurchaseAmount) AS SumOfPurchaseAmount, sqryLastRewards.RealDate
FROM tbPurchases INNER JOIN sqryLastRewards ON tbPurchases.MemberID = sqryLastRewards.CustomerID
WHERE (((tbPurchases.PurchaseDate) Between DateSerial(Year(Now()),1,1) And Now() And (tbPurchases.PurchaseDate)>[sqryLastRewards]![RealDate]))
GROUP BY tbPurchases.MemberID, sqryLastRewards.RealDate;


MemberIDNumPurchasesSumOfPurchaseAmountRealDate
1​
6​
$1,915.62​
2/4/2020​
26347​
5​
$1,236.25​
2/6/2020​

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​
5624​
1​
1/24/2020​
$55.00​
5623​
1​
1/24/2020​
$35.00​
5622​
1​
1/24/2020​
$65.00​
5621​
1​
1/24/2020​
$2,500.00​
5620​
1​
1/24/2020​
$15.00​
5625​
1​
1/24/2020​
$95.00​
1​
1​
1/9/2020​
$150.99​

Going by this data, you can see that both MemberID's had multiple purchases on the same days. Instead of counting these purchases as one each, I need to count the different days purchases were made.

EXPECTED RESULTS FROM qryCountCurrentYear:
qryCountCurrentYear



MemberIDNumPurchasesSumOfPurchaseAmount
1​
3​
$1,915.62​
26347​
0​
$0.00​

For MemberID = 1, 6 purchases were made, however three of them happened on 2/13/2020 and 2 happened on 2/6/2020. Each of those should be counted as 1 purchase.
For MemberID = 2634, 5 purchases were made, however all five happened on the same date, making them count as 1, BUT, they all happened on the same date as Rewards were issued, so they aren't > tblRewards.IssueDate, so they don't count at all. So this member technically has 0 purchases.

The SumOfPurchaseAmount DOES need to count all purchases > tblRewards, so MemberID=1 has all purchases counted, because even though they occurred on the same date, they count toward tblRewards.IssueAmount. MemberID=26347, however, has had zero purchases on zero days, so they have a zero balance accrued for Rewards credits.
 

plog

Banishment Pending
Local time
Today, 06:44
Joined
May 11, 2011
Messages
11,611
I'm curious, why does the data set make this easier on you to create the SQL statement?

In general, it quickly cuts through the ambiguity. People think they are better communicators than they actually are (especially via writing and with technical issues)--what they are saying makes sense to them because they completely understand the issue.

Example data is a psuedo-concrete way to demonstarte what you are talking about. Then when I do have a question I can point to a specific data item (like I did when you finally did post sample data) and you can walk me through the logic then.

Lastly, your marriage to your non-working SQL is not helpful. Its easier to build queries from the ground up than try and understand, debug and fix a partially working (read: worthless) query.

Which brings us to your current sample data--I don't want to build a new query off your non-working ones. What happened to tblRewards? What is RealDate? Why am I now writing 2 queries? Quit trying to piecemeal me there.

Here's what I need:

A. Give me sample data from tblRewards and tbPurchases. Include field names and enough data to cover all cases.

B. Expected reslts of A. Give me 1 set of data that shows what all the values you want for every MemberID in A--don't break it up into 3 or 4 expected queries it should all go into 1.
 

BeardedSith

Member
Local time
Today, 07:44
Joined
Feb 5, 2020
Messages
73
Ok, let's start over from the beginning. That might make this easier to demonstrate with the data.

tbPurchases:

IDMemberIDPurchaseDatePurchaseAmount
563812/13/2020$500.00
563712/13/2020$1,000.00
563612/13/2020$100.00
563512/11/2020$79.99
563412/6/2020$85.63
563312/6/2020$150.00
5631263472/6/2020$586.25
5630263472/6/2020$150.00
5629263472/6/2020$50.00
5628263472/6/2020$300.00
5627263472/6/2020$150.00
562612/3/2020$15.00
562411/24/2020$55.00
562311/24/2020$35.00
562211/24/2020$65.00
562111/24/2020$2,500.00
562011/24/2020$15.00
562511/24/2020$95.00
111/9/2020$150.99

tblRewards


IDCustomerIDIssueDateIssueAmount
6008​
26347​
2/6/2020​
$100.00​
6007​
1​
2/4/2020​
$100.00​

EXPECTED RESULTS:
qryCalculations


MemberIDNumPurchasesSumOfPurchaseAmountRealDateIsEligibleEligibleAmount
1​
3​
$1,915.62​
2/4/2020
0​
$0.00​
26347​
0​
$0.00​
2/6/2020
0​
$0.00​

Explanation of fields:
MemberID - Self Explanatory. It's what links all the tables together. CustomerID = MemberID
NumPurchases - A count of unique purchase dates that are > RealDate
SumOfPurchaseAmount - Total of the purchases where tbPurchases.PurchaseDate > RealDate
RealDate - This is a calculated date that is the greater of Jan 1st of the Current Year OR tblRewards.IssueDate. Using this expression:
Code:
RealDate: FormatDateTime(IIf(Max([tblRewards]![IssueDate])<DateSerial(Year(Now()),1,1),DateSerial(Year(Now()),1,1),Max([tblRewards]![IssueDate])),2)
IsEligible - Expression that outputs a 1 or 0 based on criteria in tblOptions. Using this expression:
Code:
IsEligible: IIf([qryCountCurrentYear]![NumPurchases]>=[tblOptions]![NumPurchases],1,0)
EligibleAmount - If the customer is Eligible for a Reward Credit, this calculation is 10% of total amount purchased since tblRewards.IssueDate (on unique dates) or $100.00, whichever is less. Based on this expression:
Code:
EligibleAmount: IIf([qryCountCurrentYear]![SumOfPurchaseAmount]>[tblOptions]![MaxIssue],[tblOptions]![MaxIssue],[qryCountCurrentYear]![SumOfPurchaseAmount])

BACKGROUND STUFF
Here is the code for qryCalculations:
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;

The end goal is to have ONE query that calculates all this information and outputs the $ amount of the Reward Credit, the # of purchases since the last Reward Credit, and a 1 or 0 to determine if they are truly eligible. Then my Append query will issue them a reward credit. Currently I have this all split into several queries (all listed in this post earlier) that perform individual tasks, that I would prefer to have all in one query.

I have the following queries currently doing all these individual actions (that I'd like incorporated into one query):
sqryLastRewards - You helped me do this earlier in the thread (thanks!). This outputs the follow:
sqryLastRewards

CustomerIDRealDate
1​
2/4/2020
24554​
1/1/2020
24557​
1/1/2020

Using this SQL:
Code:
SELECT tblRewards.CustomerID, FormatDateTime(IIf(Max([tblRewards]![IssueDate])<DateSerial(Year(Now()),1,1),DateSerial(Year(Now()),1,1),Max([tblRewards]![IssueDate])),2) AS RealDate
FROM tblRewards
GROUP BY tblRewards.CustomerID;

qryCountCurrentYear - This query counts the # of purchases that a MemberID had since their last tblRewards.IssueDate. Output is as such:
qryCountCurrentYear


MemberIDNumPurchasesSumOfPurchaseAmountRealDate
1​
6​
$1,915.62​
2/4/2020
26347​
5​
$1,236.25​
2/6/2020

And this is the SQL:
Code:
SELECT tbPurchases.MemberID, Count(*) AS NumPurchases, Sum(tbPurchases.PurchaseAmount) AS SumOfPurchaseAmount, sqryLastRewards.RealDate
FROM tbPurchases INNER JOIN sqryLastRewards ON tbPurchases.MemberID = sqryLastRewards.CustomerID
WHERE (((tbPurchases.PurchaseDate) Between DateSerial(Year(Now()),1,1) And Now() And (tbPurchases.PurchaseDate)>[sqryLastRewards]![RealDate]))
GROUP BY tbPurchases.MemberID, sqryLastRewards.RealDate;

This is where the problem exists. Count(*) within this query is counting ALL purchases a MemberID made since qryLastRewards.RealDate, instead of purchases that occurred on a unique dates. In the EXPECTED RESULTS above, you can see MemberID=1 has actually only had 3 dates where purchases occurred, instead of the 6 that's currently being output. And MemberID=26347 has zero because all of their purchases happened on the same day as their qryLastRewards.RealDate and not > qryLastRewards.RealDate and not unique dates. All of the five purchases MemberID=26347 made on 2/6/2020 would count as 1, instead of 5.

And finally, just in case you want to see the data in tblOptions, I'll go ahead and include it here:
tblOptions

IDMaxIssueNumPurchasesPerDiscount
1​
$100.00​
6​
10.00%​
 

plog

Banishment Pending
Local time
Today, 06:44
Joined
May 11, 2011
Messages
11,611
You just can't not write a thesis and include code can you? I've only partially skimmed all your novels, including the last one. From that skimming though, I've ascertained that somehow the current year needs to be used on tbPurchases. Unfortunately, all your A data is in 2020 which means you have not demonstrated this aspect with data.

Further, new aspects keep creeping into the data--which is fine if you give me data to work with. Since both records contain the exact same values for IsEligible and EligibleAmount I cannot determine how the are to be calculated.

So, please post A and B data that demonstrate all cases of what you want to achieve. Or start a new thread and perhaps someone who prefers to read paragraphs of logic than discern it from data will assist you.
 

BeardedSith

Member
Local time
Today, 07:44
Joined
Feb 5, 2020
Messages
73
You just can't not write a thesis and include code can you? I've only partially skimmed all your novels, including the last one. From that skimming though, I've ascertained that somehow the current year needs to be used on tbPurchases. Unfortunately, all your A data is in 2020 which means you have not demonstrated this aspect with data.

Further, new aspects keep creeping into the data--which is fine if you give me data to work with. Since both records contain the exact same values for IsEligible and EligibleAmount I cannot determine how the are to be calculated.

So, please post A and B data that demonstrate all cases of what you want to achieve. Or start a new thread and perhaps someone who prefers to read paragraphs of logic than discern it from data will assist you.
Sigh...

@plog - First off, you haven't even read what I wrote? Are you kidding me, dude? IF you read what I wrote, you could 100% understand what it is I need. Creeping new aspects into the database? Nope. Had you read ANY of what I wrote, you would understand that this was the requirement from the beginning.

I am sorry for trying to explain the situation instead of just reading your mind. You want A & B? I've given A & B several times, but again, you're not reading what I write.

This has been my first time asking for help on this forum, and thus far, you have not represented this community particularly well. Don't get pissy with me when you A) Won't read what I write, and B) lambaste me for trying to be as detailed and thorough as possible. There some A & B's for you.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:44
Joined
Feb 28, 2001
Messages
26,996
BeardedSith, you told us a lot of things, and the problem we have when looking at what you posted is determining relevance of each part to whatever it is that you REALLY wanted to know. This is a "forest and trees" issue and you have presented us with every little tree, then you wonder why we have trouble with the forest. It takes TIME to read what you posted (and I commend your typing skills for it) but you have presented an overwhelming pile of explanatory data for fields that really have nothing to do with the question you started to ask. There IS such a thing as "too much of a good thing" and I believe you have provided a truly excellent example of that.

I also commend you on having such a complete description of your overall problem at such a detailed level. It betrays considerable depth of knowledge of the problem. So many people come here seeking help and have no clue as to the business model or how the database represents a model of the actual business. You have clearly done a thorough preliminary analysis and some fairly impressive design work.

I understand that the SQL is vexing you. Sometimes it vexes me too. But plog's complaint and mine would be similar. You are asking how a few pieces of a particular puzzle fit together but you just dumped all 1000 pieces on the table. Many of use would like to help new members but sometimes a major part of the problem is isolating what is significant to your specific question. The amount of descriptive data you posted is important to you but not all of it is important to the specific question. Forgive us if you have overwhelmed us, but we don't normally take that deep a dive into a problem.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 11:44
Joined
Jul 9, 2003
Messages
16,244
@plog - First off, you haven't even read what I wrote? Are you kidding me, dude?

I'm replying in my capacity as a moderator and I just like to say I think you're getting some excellent help. I've read through your posts and I am confused myself! You should listen to Plog and answer his questions. Also, exercise a bit of patience.
 

Users who are viewing this thread

Top Bottom