Reading Distinct "Short Dates" from Table and counting them

[BeardedSith]

I have a query where I'm trying to read only the short date out of a table's "PurchaseDate" column (which is formatted as short date, but includes the time when input from a form) and count those unique short dates.

Details:
I have a database that tracks customer rewards. When a customer comes in on 6 separate days and makes purchases, it counts as 1 "purchase" when a customer makes multiple purchases on a single day. We need 6 unique dates, no matter the # of purchases made. The issue I'm having is when the date is saved to my table, it includes the time. So it's nearly impossible that two date entries will be identical, making my counts off. So I'm trying to reformat/trim/whatever the PurchaseDate column THEN count.

Here's what I have now, and it's counting every entry as a single "PurchaseDate" entry as a distinct entry:

Testing Query: This query isn't actually something I'm using, it's just to test the process of calculating each "PurchaseDate" entry as a DISTINCT entry

SELECT DISTINCT sqryCalc2.MemberID, Count(Format([sqryCalc2]![DatePurchase],"Short Date")) AS DatePurchase
FROM sqryCalc2
GROUP BY sqryCalc2.MemberID;

This is the "real" query I'm trying to accomplish this with:

SELECT sqryCalc1.ID, Count(sqryCalc2.MemberID) AS NumPurchases, Sum(Nz([DailyPurchaseTotal],0)) AS SumOfPurchaseAmount, sqryCalc1.RealDate, Count([sqryCalc2]![DatePurchase]) AS DateCount
FROM sqryCalc1 LEFT JOIN sqryCalc2 ON sqryCalc1.ID = sqryCalc2.MemberID
GROUP BY sqryCalc1.ID, sqryCalc1.RealDate;

Note: "Count([sqryCalc2]![DatePurchase]) AS DateCount" is a testing column that I'm using to try to get this to work all in one query.

Follow-up question: Is it possible to pull DISTINCT values AND Non-DISTINCT values (two different columns) in the same SQL statement?

 

[jdraw]

If you have included time with dates, you can use the intrinsic function (DateValue) to get the Date only portion.

?now
29-Jul-20 7:48:56 AM

?datevalue(now)
29-Jul-20

 

[BeardedSith]

If you have included time with dates, you can use the intrinsic function (DateValue) to get the Date only portion.

?now
29-Jul-20 7:48:56 AM

?datevalue(now)
29-Jul-20

How does that fit into my queries, though? I've tried DateValue and I get a data mismatch error. That leads me to believe it's counting before it's converting.

SELECT sqryCalc1.ID, Count(sqryCalc2.MemberID) AS NumPurchases, Sum(Nz([DailyPurchaseTotal],0)) AS SumOfPurchaseAmount, sqryCalc1.RealDate, Count(DateValue([sqryCalc2]![DatePurchase])) AS DateCount
FROM sqryCalc1 LEFT JOIN sqryCalc2 ON sqryCalc1.ID = sqryCalc2.MemberID
GROUP BY sqryCalc1.ID, sqryCalc1.RealDate;

 

[jdraw]

Show your query code. It would be helpful if you posted cut down database with only the relevant tables and query involved.

Test table:
ForumDateIssue ForumDateIssue

id	plannedEnd	plannedStart
2​	01-Aug-17 1:00:00 PM​	01-Aug-17 6:00:00 AM​
3​	29-Aug-17 10:00:00 PM​	27-Aug-17 9:00:00 AM​
4​	31-Aug-17 11:00:00 PM​	31-Aug-17 8:30:00 PM​
5​	29-Aug-17 12:17:37 PM​	27-Aug-17 8:25:30 AM​

Count and Distinct DateOnly

SELECT distinct DateValue([plannedStart]) AS D1
,count(d1) as Counts
FROM ForumDateIssue
Group by DateValue([plannedStart]);

Result:
Query59

D1	Counts
01-Aug-17​	1​
27-Aug-17​	2​
31-Aug-17​	1​

 

[BeardedSith]

Attached is a VERY stripped down database with the data and queries in question.

Looking at sqryCalc2, there are six different entries under MemberID 24877. Currently it counts all of these as individual [DatePurchase]'s. as a distinct entry. It should count these as FIVE, and not SIX.

Here is the query for sqryCalc2:

SELECT sqryCalc0.MemberID, sqryCalc0.DatePurchase, Sum(sqryCalc0.PurchaseAmount) AS DailyPurchaseTotal
FROM sqryCalc0 INNER JOIN sqryCalc1 ON sqryCalc0.MemberID = sqryCalc1.ID
WHERE (((sqryCalc0.DatePurchase)>=[RealDate] And (sqryCalc0.DatePurchase)>=CDate("1/1/" & Year(Date()))))
GROUP BY sqryCalc0.MemberID, sqryCalc0.DatePurchase;

And here's the query that actually does the counting, sqryCalc3:

SELECT sqryCalc1.ID, Count(sqryCalc2.MemberID) AS NumPurchases, Sum(Nz([DailyPurchaseTotal],0)) AS SumOfPurchaseAmount, sqryCalc1.RealDate
FROM sqryCalc1 LEFT JOIN sqryCalc2 ON sqryCalc1.ID = sqryCalc2.MemberID
GROUP BY sqryCalc1.ID, sqryCalc1.RealDate;

 

[arnelgp]

see Query1 and Query2

 

[BeardedSith]

I *think* that might just do what I need it to. Unfortunately I have to cut out of work (wife's birthday) so I won't know for sure until tomorrow. Thanks a million, @arnelgp

 

[arnelgp]

to all of us here, Happy birthday to your wife!