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
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;
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;
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.
It's there.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.
ID | CustomerID | IssueDate | IssueAmount |
1 | 1 | 2/2/2020 | $100.00 |
2 | 2 | 2/3/2020 | $46.63 |
ID | MemberID | PurchaseDate | PurchaseAmount |
1 | 1 | 11/12/2019 | $15.00 |
2 | 1 | 11/13/2019 | $1500.00 |
3 | 1 | 12/12/2019 | $1000.00 |
4 | 1 | 1/22/2020 | $5.00 |
5 | 1 | 1/29/2020 | $100.00 |
6 | 1 | 2/3/2020 | $100.00 |
7 | 2 | 2/7/2020 | $100.00 |
8 | 2 | 2/7/2020 | $100.00 |
9 | 2 | 2/8/2020 | $100.00 |
10 | 2 | 2/9/2020 | $100.00 |
11 | 1 | 2/10/2020 | $100.00 |
12 | 2 | 2/10/2020 | $100.00 |
13 | 2 | 2/10/2020 | $5.00 |
14 | 1 | 2/12/2020 | $100.00 |
15 | 1 | 2/13/2020 | $100.00 |
MemberID | SumOfPurchaseAmount | CountOfIssueDate |
1 | $400.00 | 4 |
2 | $505.00 | 6 |
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!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?
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;
CustomerID | RealDate |
---|---|
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 |
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;
MemberID | NumPurchases | SumOfPurchaseAmount | RealDate |
---|---|---|---|
1 | 6 | $1,915.62 | 2/4/2020 |
26347 | 5 | $1,236.25 | 2/6/2020 |
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 |
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 |
MemberID | NumPurchases | SumOfPurchaseAmount |
---|---|---|
1 | 3 | $1,915.62 |
26347 | 0 | $0.00 |
I'm curious, why does the data set make this easier on you to create the SQL statement?
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 |
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 |
ID | CustomerID | IssueDate | IssueAmount |
---|---|---|---|
6008 | 26347 | 2/6/2020 | $100.00 |
6007 | 1 | 2/4/2020 | $100.00 |
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 |
RealDate: FormatDateTime(IIf(Max([tblRewards]![IssueDate])<DateSerial(Year(Now()),1,1),DateSerial(Year(Now()),1,1),Max([tblRewards]![IssueDate])),2)
IsEligible: IIf([qryCountCurrentYear]![NumPurchases]>=[tblOptions]![NumPurchases],1,0)
EligibleAmount: IIf([qryCountCurrentYear]![SumOfPurchaseAmount]>[tblOptions]![MaxIssue],[tblOptions]![MaxIssue],[qryCountCurrentYear]![SumOfPurchaseAmount])
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;
CustomerID | RealDate |
---|---|
1 | 2/4/2020 |
24554 | 1/1/2020 |
24557 | 1/1/2020 |
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;
MemberID | NumPurchases | SumOfPurchaseAmount | RealDate |
---|---|---|---|
1 | 6 | $1,915.62 | 2/4/2020 |
26347 | 5 | $1,236.25 | 2/6/2020 |
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;
ID | MaxIssue | NumPurchases | PerDiscount |
---|---|---|---|
1 | $100.00 | 6 | 10.00% |
Sigh...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.
@plog - First off, you haven't even read what I wrote? Are you kidding me, dude?