Count() Unique Dates Between Two Variables

[BeardedSith]

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
5639	1	2/14/2020	$100.00
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	$2015.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:

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.

[Gicu]

Use a new select distinct query (or a totals query grouping by memberid and date) with your desired between dates condition then another in which you group by memberid and count (date).
Cheers,

[CJ_London]

before anyone gets too deep into this - see the cross post here https://www.access-programmers.co.uk....309367/page-3 - already up to 44 posts

@BeardedSith - it is not wrong to cross post but it is considered a general courtesy when cross posting to advise you are doing this and providing a link. See this link for the reasons why https://www.excelguru.ca/content.php?184