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

[BeardedSith]

Hi there!

First off, I'm a long-time reader but first time account-creator and poster. So, hi!

I have a query I'm struggling to create. I need to SELECT and COUNT records from tbPurchases where tbPurchases.PurchaseDate is > Last(tblRewards.IssueDate) WHERE CustomerID = tbPurchases.MemberID. Then I need a total of tbPurchases.PurchaseAmount. I'm not the greatest at SQL so the fact that this will likely require aliases and sub-queries, I'm at a total loss. I tried to accomplish the same feat using multiple queries, but I always get backed up with trying to Count() and use Last().

BACKGROUND:

I'm creating a Rewards System database for my employer. There are several tables to note:

• tblMembers - This table has all membership/customer data such as address, phone, etc.
• tblOptions - This table includes several options such as the number of purchases within a calendar year to qualify for a Rewards Credit. (currently 6)
• tblRewards - When a customer meets the requirements to be issued a Rewards Credit, it will add a row to this table including the amount of the rewards credit (10% of their purchases since their last Rewards Credit, up to $100.00 - these are set in tblOptions also).
• tbPurchases - yeah, I left out the "L" in "tbl", oops. This is where each purchase is cataloged. Includes total spent, date, customer ID, etc.

I also have several queries that perform similar actions but not fully what I need:

• qryCountCurrentYear - This will count the number of purchases the customer has had in the current calendar year.

  • SELECT qryCountCurrentYear.MemberID, FormatCurrency([qryCountCurrentYear]![SumOfPurchaseAmount]*[tblOptions]![PerDiscount]) AS RewardAmount, qryCountCurrentYear.NumPurchases
    FROM qryCountCurrentYear, tblOptions
    WHERE (((qryCountCurrentYear.MemberID)=[Forms]![frmRewards]![txtID]));

• qryEligibiles - This pulls a list of potentially eligible customers from qryCountCurrentYear (above) using options from tblOptions.

  • SELECT qryCountCurrentYear.MemberID, qryCountCurrentYear.NumPurchases, FormatDateTime([LastOfPurchaseDate],2) AS LastPurchase, FormatCurrency([qryCountCurrentYear]![SumOfPurchaseAmount]*[tblOptions]![PerDiscount]) AS EligibleAmount, qryCountCurrentYear.SumOfPurchaseAmount AS TotalSpent
    FROM qryCountCurrentYear, tblOptions
    WHERE (((qryCountCurrentYear.NumPurchases)>=[tblOptions]![NumPurchases]));

• qryEligibility - This pulls the list from qryEligibles, find when their last reward was issued and how much the actual reward should be based on the calculation from qryEligibles and the Max Rewards that can be issued from tblOptions.

  • SELECT qryEligibles.MemberID, FormatCurrency(IIf([qryEligibles]![EligibleAmount]>[tblOptions]![MaxIssue],[tblOptions]![MaxIssue],[qryEligibles]![EligibleAmount])) AS Amount, Nz([tblRewards]![IssueDate],"1/1/" & Year(Now())) AS LastReward
    FROM tblOptions, qryEligibles INNER JOIN tblRewards ON qryEligibles.MemberID = tblRewards.CustomerID;

These queries perform similar calculations to what I need to accomplish, but where I'm struggling is grabbing the purchases since the last time the customer was issued a rewards credit. Any help y'all can provide would be immensely helpful in finishing this project.

RELATIONSHIPS

Attached is an image of my table relationships.

 

[plog]

First, never use Last()--don't even acknowledge its existence. Its a horrible function that doesn't do what a normal person would expect it to. Instead use MAX(). So, to get the last reward date per Customer use this query:

SELECT CustomerID, MAX(IssueDate) AS LastRewardDate
FROM tblRewards
GROUP BY CustomerID

Give that query a name (e.g. sub_LastReward). Then for the initial SELECT/COUNT query with the WHERE clause you posted, JOIN it to tblPurchases like so:

...
FROM tblPurchases
INNER JOIN sub_LastReward ON sub_LastReward.CustomerID=tblPurchases.MemberID
AND sub_LastReward.LastRewardDate<=tblPurchases.PurchaseDate
...

You no longer need the WHERE--the way we write the JOIN takes care of everything. That will be the first query you mention. However, your next query is ambigous:

Then I need a total of tbPurchases.PurchaseAmount.

If you simply need a total of all purchases that's super easy, just SUM(PurchaseAMount). But I'm guessing you either want it by MemberID, after the last Reward date or in the first query you mentioned--perhaps all 3 of those things.

Hopefully that first query I posted will spur you to the answer you need. If not, please clarify the second query you want.

 

[BeardedSith]

I won't have time to look at this until tomorrow (off work now), but to answer your final question, I need the SUM(tbPurchases.PurchaseAmount) by MemberID where tbPurchases.PurchaseDate > tblRewards.MAX(IssueDate). Does that sytax seem like it'll work?
I'll tinker with this in the morning. Either way, thanks for the help!

 

[BeardedSith]

Alright, I made a whole lot of progress today, but I ended with another issue with my first query (qryCountCurrentYear) where it's not calculating the number of purchases AFTER the last Rewards Issue Date.

Talking the SQL statement out, it makes sense. But it's not only counting records where the PurchaseDate is > EligibilityDate. It's including all records where those purchases happened in the current year (DateSerial(Year(Now()),1,1)

It may be a really simple fix, but my brain is so twisted around with these queries. I'm not great at this to begin with, but when the confusion sets in...phew. lol

SELECT tbPurchases.MemberID, Count(*) AS NumPurchases, Sum(tbPurchases.PurchaseAmount) AS SumOfPurchaseAmount
FROM qryEligibilityDate INNER JOIN tbPurchases ON qryEligibilityDate.ID = tbPurchases.MemberID
WHERE (((tbPurchases.PurchaseDate) Between DateSerial(Year(Now()),1,1) And Now() And (tbPurchases.PurchaseDate)>[qryEligibilityDate]![EligibleDate]))
GROUP BY tbPurchases.MemberID;

 

[plog]

WHERE (((tbPurchases.PurchaseDate) Between DateSerial(Year(Now()),1,1) And Now() And (tbPurchases.PurchaseDate)>[qryEligibilityDate]![EligibleDate]))

But it's not only counting records where the PurchaseDate is > EligibilityDate. It's including all records where those purchases happened in the current year (DateSerial(Year(Now()),1,1)

This current year criteria is a new aspect to this that you haven't mentioned before, and haven't adequately explained. I see how its working, and I see your explanation of how you think its working, but no explanation of how you want it to work. Here's what that criteria means translated into english:

Include records that occur in the current year AND before the current date AND after the eligibilty date

Please explain what you want it to do, and perhaps include a record or two that should be included and one that should not be included to demonstrate.

 

[theDBguy]

Hi. You're already getting good help here, so I'll just say: Welcome to AWF!

Cheers!

 

[jdraw]

I will also say Welcome to AWF. plog is on top of your post, I'll will only suggest that you identify and describe what exactly you want to accomplish in simple, plain English, provide some samples of input and output, and then focus on the SQL/query(s) etc.
Good luck with your project.

 

[BeardedSith]

Hey guys! Sorry for taking so long to get back to this. I've had the flu all weekend and haven't been able to work on this project. But it's Monday, and I'm back at it.

First off, this is going to be long. SORRY! I can always share the database itself with you guys if you want, but I'd have to wipe any personal data out first.

Anyway, thanks for all the warm welcomes. I'll try to explain a little better what it is I'm trying to accomplish here.

My eventual goal for this database is to create a method/procedure that each time the end-user performs any actions in the database, the procedure runs and performs all the calculations necessary to "issue" rewards.
- Example:

Public Function ProcessRewards()
  DoCmd.SetWarnings False
  CurrentDb.Execute "qryIssueRewards"
  DoCmd.SetWarnings True
End Function

BREAKDOWN
- Basics:

• The purpose is when customers come into our store, each purchase is entered into the database. After certain conditions are met (see `tblOptions` below for those conditions) then a reward is "issued" to the customer.
• Issuing a reward consists of a table entry into `tblRewards` which includes the date of the rewards issue and the amount of the credit amount.
• All purchases that occur AFTER a reward is issued are calculated for the NEXT reward issue.

- Reward Requirements:

• Most of these requirements are saved in `tblOptions` and are subject to change, but currently they are set as such:
  • Six purchases in the same calendar year
  • Six purchases on six different dates
  • The credit will be 10% of the total the customer spent on the purchases that fit into the above guidelines up to $100.00
  • The credit's are all wiped out January 1st of each year.

- Forms: The database won't have a whole lot of forms. Most actions are performed via `frmRewards`. Other forms are only there to support this form.
- Tables: For this discussion, here are the important tables:

• tblOptions - This consists of the three options in the database.
  • MaxIssue - This is the US Dollar amount that we "cap" rewards credits. So if a customer "earns" $1000.00 in credits, the MAX we will issue is entered in this field.
  • NumPurchases - This accounts for two things and is the crux of my NEXT problem I'll need to solve (I haven't really started working on this yet). Here is a numerical value (current 6) that the customer needs to meet before we calculate their rewards issue. Currently I have it where it's only calculating six total purchases. So if a customer comes in six times in one day, that would count as six purchases and they would be authorized a reward credit. HOWEVER what I'll need to do in the future is change this from six PURCHASES to six purchases on six different DAYS. So the customer can't come in and make six purchases and be issued a reward immediately.
  • PerDiscount - This is the percentage the rewards credit is calculated off
• tblMembers - This has all the personal information and the important field that's linked to other tables here is `ID`
• tbPurchases - Yes, I left out an "L" in the name, oops! Each time a customer comes in, we enter the total amount of the purchase on `frmRewards` (image below)
• tblRewards - After the database determines the customer meets the requirements, a row is entered to this table with the CustomerID, IssueDate, and IssueAmount. IssueDate is the date of issue, IssueAmount is either XX% based on the field in `tblOptions` or the max reward credit also based off `tblOptions`.

- Queries: I have several queries to perform different actions, here's the list:

• sqryLastRewards - This is one of the queries plog helped me create. It grabs the CustomerID and Max(IssueDate) under 'tblRewards'

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

• qryEligibilityDate - This uses `sqryLastRewards` and returns an "effective" eligibility date using the following expression:

  • EligibleDate: Nz(IIf([sqryLastRewards]![MaxOfIssueDate]>DateSerial(Year(Now()),1,1),[sqryLastRewards]![MaxOfIssueDate],DateSerial(Year(Now()),1,1)))

  • Each customer is only eligible for a reward credit during the current calendar year. So each year that passes, even if a customer hasn't had a reward issued in several years, they may meet the other requirements in `tblOptions` but if the purchases weren't made in the current calendar year, then they won't count toward any potential reward credit.

  • SELECT tblMembers.ID, Nz(IIf([sqryLastRewards]![MaxOfIssueDate]>DateSerial(Year(Now()),1,1),[sqryLastRewards]![MaxOfIssueDate],DateSerial(Year(Now()),1,1))) AS EligibleDate
    FROM sqryLastRewards RIGHT JOIN tblMembers ON sqryLastRewards.CustomerID = tblMembers.ID;

• qryCountCurrentYear - This query counts the number of purchases a customer has during the current calendar year. This uses the above `qryEligibilityDate` to determine when the "true" eligibility for another reward credit occurs.
  • This early in the year, there aren't a whole lot of purchases in the database, but this will retrieve all customers who have had purchases AFTER their last Eligibility Date (which is either the date of their last reward credit OR the 1/1/XXXX, depending on if they've had any purchases in the calendar year) based off `qryEligibilityDate`
  • Only customers with purchases after their eligibility date are listed in this query, using the following SQL:

    • SELECT tbPurchases.MemberID, Count(*)AS NumPurchases, Sum(tbPurchases.PurchaseAmount) AS SumOfPurchaseAmount
      FROM qryEligibilityDate INNER JOIN tbPurchases ON qryEligibilityDate.ID = tbPurchases.MemberID
      WHERE (((tbPurchases.PurchaseDate) Between DateSerial(Year(Now()),1,1) And Now() And (tbPurchases.PurchaseDate)>[qryEligibilityDate]![EligibleDate]))
      GROUP BY tbPurchases.MemberID;

    • The `Count(*)` is the problem.
• qryCalculations - This is the bread and butter query that puts everything up to this point together into a single query. This uses `qryEligibilityDate` and `qryCountCurrentYear` to establish a list of customers who are eligible for a reward credit. This is where I'm struggling.
  • This query consists of a count of the purchases based off `qryCountCurrentYear` (this is the main problem - and it needs to change to something else?)
  • It also establishes the Eligibility Date based on `qryEligibilityDate`
  • Two expressions exist and are working properly but I may need to change the method I accomplish these actions

    • IsEligible: IIf([qryCountCurrentYear]![NumPurchases]>=[tblOptions]![NumPurchases],1,0)

      • If a customer is eligible for a reward credit, a 1 will be returned. Otherwise, a 0. This probably isn't the best way to accomplish this and is still a process in work. The issue I'm having with this so far is that `qryEligibilityDate.EligibleDate` is returning the proper date of the last reward OR the first of the year but the Number of Purchases from `qryCountCurrentYear` is giving ALL purchases since the beginning of the year instead of those since the last Reward Issue Date.

    • EligibleAmount: IIf([qryCountCurrentYear]![SumOfPurchaseAmount]>[tblOptions]![MaxIssue],[tblOptions]![MaxIssue],[qryCountCurrentYear]![SumOfPurchaseAmount])

      • This returns the "actual" amount of a potential Reward credit.
• qryIssueRewards - This is the "Append" query that, based on `qryCalculations` will create the row in `tblRewards` based on the information returned in the query. This is what will be "performed" via a method/procedure throughout the database as the end-user does various tasks.

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

 

[BeardedSith]

EXAMPLE DATA
- Relationship breakdown in original post
tblMembers:

ID	FirstName	LastName	Address	ZipCode	E-mail Address	MobilePhone	HomePhone	Country/Region	Notes	JoinedDate
1	Jason	Something	110 Something	44485	jason@something.net	222-555-7777	222-556-8888	US	Normal hair	1/1/2019
2	John	Locke	111 Something	44485	john@something.net	222-555-8888	222-556-9999	US	Bald	2/2/2019
3	Jake	Funtime	112 Something	44485	jake@funtimes.net	222-555-9999	222-556-0001	US	Too much hair	3/3/2019

tblOptions:

ID	MaxIssue	NumPurchases	PerDiscount
1	$100.00	6	10.00%

tblRewards:

ID	CustomerID	IssueDate	IssueAmount
1	1	2/2/2020	$100.00
2	2	2/3/2020	$46.63

tbPurchases:

ID	MemberID	PurchaseDate	PurchaseAmount
1	1	1/9/2020	$15.00
2	1	1/10/2020	$1500.00
3	1	1/12/2020	$2999.99
4	1	1/22/2020	$4.56
5	1	1/29/2020	$5.99
6	1	2/3/2020	$10.99
7	2	2/7/2020	$34.56
8	2	2/7/2020	$69.99
9	2	2/8/2020	$299.99
10	2	2/9/2020	$65.99
11	1	2/10/2020	$79.99
12	2	2/10/2020	$1.99
13	2	2/10/2020	$5.00
14	1	2/12/2020	$1599.99

Using the data above, we can establish that:
- Customer #1 has met the requirements to be issued rewards. This occurred on their sixth purchase on 2/3/2020, and the total amount spent was $4542.52. Going by the options set forth in tblOptions, 10% of this total would be $454.25, but since the MaxIssue in tblOptions is set at $100.00, they would be issued a $100.00 credit.
- Customer #2 has the correct number of purchases, BUT they don't occur on six different dates, so they DO NOT meet the requirements to be issued a reward. Currently the database is set up that if there are six total purchases, then they could be issued a reward. This is something I'm working on currently. Any ideas would be helpful on this.
- Customer #1 has two purchases AFTER his last Eligibility Date, which would be 2/3/2020 (when the last reward was issued). Once this customer makes four more purchases on different dates, they would be authorized another reward credit.

 

[BeardedSith]

Hi there! I just re-read my awesomely long posts describing my database and realized I never really got to the core of what I'm asking for help with.

TL;DR version:

In this query (below), I need to change Count(*) to Count(Where tbPurchases.PurchaseDate is between (Max(tblRewards.IssueDate WHERE CustomerID = tbPurchases.MemberID)).

Being as though tblRewards isn't an original part of the query, I'm not entirely sure how to go about writing this up. I might be able to fumble my way with multiple more queries, but honestly I'd like to do this the most correct way, and the most efficient way. The multiple Count(*)'s and WHERE clauses are confusing the heck out of me. Thanks!

SELECT tbPurchases.MemberID, Count(*)AS NumPurchases, Sum(tbPurchases.PurchaseAmount) AS SumOfPurchaseAmount
FROM qryEligibilityDate INNER JOIN tbPurchases ON qryEligibilityDate.ID = tbPurchases.MemberID
WHERE (((tbPurchases.PurchaseDate) Between DateSerial(Year(Now()),1,1) And Now() And (tbPurchases.PurchaseDate)>[qryEligibilityDate]![EligibleDate]))
GROUP BY tbPurchases.MemberID;

 

[plog]

Enough words, for me to help you now I will require data. Please provide 2 sets:

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.

Again, no explanation, just 2 sets of data--starting and expected results.

 

[BeardedSith]

What do you need differently from this? I went through and edited out anything overly personal in the data, so I'll just share both of the split files on my Google Drive.

Google Drive Folder

 

[plog]

We can call that A. Now I will need B.

What data do you expect the query to return based on the data in the RewardsBackEnd1.accdb database?

 

[BeardedSith]

I need to change Count(*) to Count(Where tbPurchases.PurchaseDate is between (Max(tblRewards.IssueDate WHERE CustomerID = tbPurchases.MemberID)).

What I want to accomplish, in addition to what I already have working in the database (thanks for that help earlier!), is to pull all purchases for a customer between the date of their last reward issue and Now(). If they haven't had a reward issue (in tblRewards) since the first of the current calendar year, then I need the purchases between now() and DateSerial(Year(Now()),1,1) (Jan 1st of the current year). Then I need a count of those records. The query for everything else is already written up (see post your first one today), but the "Count(*)" part needs to only count those records since either A) Their last Reward Issue from tblRewards, or B) if they haven't had any Rewards issued in the current calendar year or at all, then Jan 1st of the current year.

In the example data posted above HERE, CustomerID 1 should return a count of 2, not 8. Sorry if I'm being ambiguous, I'm honestly trying to share as much as humanly possible.

Thanks again, plog!

 

[plog]

Last try:

Enough words, for me to help you now I will require data. Please provide 2 sets:

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.

Again, no explanation, just 2 sets of data--starting and expected results.

 

[BeardedSith]

plog, I'm not entirely sure what you want for data? I thought my previous posts explained what I wanted.

Starting data from tables: Google Drive, or example data above.
Ending data from query: MemberID, Sum of PurchaseAmount, and a Count of purchases in tbPurchases where PurchaseDate > Jan 1st OR most recent IssueDate from tblRewards
Using this data example:
tblRewards:

ID	CustomerID	IssueDate	IssueAmount
1	1	2/2/2020	$100.00
2	2	2/3/2020	$46.63

tbPurchases:

ID	MemberID	PurchaseDate	PurchaseAmount
1	1	1/9/2020	$15.00
2	1	1/10/2020	$1500.00
3	1	1/12/2020	$2999.99
4	1	1/22/2020	$4.56
5	1	1/29/2020	$5.99
6	1	2/3/2020	$10.99
7	2	2/7/2020	$34.56
8	2	2/7/2020	$69.99
9	2	2/8/2020	$299.99
10	2	2/9/2020	$65.99
11	1	2/10/2020	$79.99
12	2	2/10/2020	$1.99
13	2	2/10/2020	$5.00
14	1	2/12/2020	$1599.99

EXPECTED OUTPUT

MemberID	SumOfPurchaseAmount	CountOfIssueDate
1	$1690.97	2
2	$477.52	6

And one more time, here is the original query I need changed.

SELECT tbPurchases.MemberID, Count(*)AS NumPurchases, Sum(tbPurchases.PurchaseAmount) AS SumOfPurchaseAmount
FROM qryEligibilityDate INNER JOIN tbPurchases ON qryEligibilityDate.ID = tbPurchases.MemberID
WHERE (((tbPurchases.PurchaseDate) Between DateSerial(Year(Now()),1,1) And Now() And (tbPurchases.PurchaseDate)>[qryEligibilityDate]![EligibleDate]))
GROUP BY tbPurchases.MemberID;

If this STILL isn't what you're asking for, then please rephrase your request, because I'm clearly not understanding what "Expected results if A. show me the data you expect the query to produce when you feed it the data from A " means.

 

[plog]

Now we have thrown away the database and are using your last post as A and B. Which is fine, except it doesn't jive.

For MemberID=2 tbPurchases has 6 records > 2/3/2020 (its issue date in tblRewards) totaling 477.52 not 475.53. That's a difference of 1.99 which is the amount of ID=12 in tbPurchases. Why is that record not included in the expected results?

For MemberID=2 tbPurchases has 3 records > 2/2/2020 (its issue date in tblRewards) totaling 1690.97; that is 1 more than its CountOfIssueDate and 10.99 less than its expected SumOfPurchaseAMount. Why is ID=6 of tbPurchses not included in the expected results?

Now you may use words to explain my issues.

 

[BeardedSith]

For MemberID=2 tbPurchases has 6 records > 2/3/2020 (its issue date in tblRewards) totaling 477.52 not 475.53. That's a difference of 1.99 which is the amount of ID=12 in tbPurchases. Why is that record not included in the expected results?

My math was wrong, I did it in my head and left some out, sorry about that. My mistake. You are correct on all accounts here.

For MemberID=2 tbPurchases has 3 records > 2/2/2020 (its issue date in tblRewards) totaling 1690.97; that is 1 more than its CountOfIssueDate and 10.99 less than its expected SumOfPurchaseAMount. Why is ID=6 of tbPurchses not included in the expected results?

I think you meant MemberID=1, in which case you are correct, there are 3 records, I'm an idiot and can't count lol. Sorry again.

In both cases, you are absolutely, 100% correct on my math mistakes. Sorry again about that.

One other thing to note, CustomerID=1 was issued $100.00 instead of 10% of 1690.97 because $100.00 is the maximum amount of Rewards we issue. The 10% is also a variable. Both are set in tblOptions. That doesn't really affect the question, but it may cause some confusion about the data above.

I'll edit the data examples I listed above to show the correct amount (your math > my math).

 

[plog]

No problem on the data. The below query will take the data you provided and produce the data you expected:

SELECT MemberID, SUM(PurchaseAmount) AS SumOfPurchaseAmount, COUNT(MemberID) AS CountOfIssueDate
FROM tbPurchases
INNER JOIN tblRewards ON CustomerID=MemberID
WHERE PurchaseDate>IssueDate
GROUP BY MemberID

 

[BeardedSith]

JOIN expression not supported.