Reading Distinct "Short Dates" from Table and counting them (1 Viewer)

BeardedSith

Member
Local time
Today, 18:34
Joined
Feb 5, 2020
Messages
73
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
Code:
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:
Code:
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

Super Moderator
Staff member
Local time
Today, 18:34
Joined
Jan 23, 2006
Messages
15,364
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

Member
Local time
Today, 18:34
Joined
Feb 5, 2020
Messages
73
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.

Code:
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

Super Moderator
Staff member
Local time
Today, 18:34
Joined
Jan 23, 2006
Messages
15,364
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

idplannedEndplannedStart
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
Code:
SELECT distinct DateValue([plannedStart]) AS D1
,count(d1) as Counts
FROM ForumDateIssue
Group by DateValue([plannedStart]);

Result:
Query59

D1Counts
01-Aug-17​
1​
27-Aug-17​
2​
31-Aug-17​
1​
 
Last edited:

BeardedSith

Member
Local time
Today, 18:34
Joined
Feb 5, 2020
Messages
73
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:
Code:
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:
Code:
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;
 

Attachments

  • RewardsDB2019.accdb
    1.7 MB · Views: 416

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:34
Joined
May 7, 2009
Messages
19,169
see Query1 and Query2
 

Attachments

  • RewardsDB2019.zip
    892.6 KB · Views: 408

BeardedSith

Member
Local time
Today, 18:34
Joined
Feb 5, 2020
Messages
73
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:34
Joined
May 7, 2009
Messages
19,169
to all of us here, Happy birthday to your wife!🎂
 

Users who are viewing this thread

Top Bottom