Count distinct dates per customer

[jimrward]

i have a simple database which i have pruned for this post
CustId, OrderDate
2, 01/01/22
1, 19/11/22
2, 19/11/22
1, 19/11/22

I am creating a query to report on as follows, to extract, CustID, Year(OrderDate), Quarter

SELECT CustID, Year(["OrderDate"]) AS YearOrder, Val(Format(["OrderDate"],"q")) AS Qtr
FROM CurrentData
WHERE (((Year(["OrderDate"]))=Year(Now())))
GROUP BY CustID, Year(["OrderDate"]), Val(Format(["OrderDate"],"q"))
ORDER BY Year(["OrderDate"]);

As one customer can make several orders on one date I would like to call this a visit and sum these up by quarter to give the number of unique visits per quarter
using the above data i would like to see, but i cant quite get the syntax right to make access happy

custid, year, qtr, visits
2, 2022, 1, 1
1, 2022, 4, 2
2, 2022, 4, 1

 

[james_lankford]

does this not work ?

SELECT CustID, Year(["OrderDate"]) AS YearOrder, Val(Format(["OrderDate"],"q")) AS Qtr, count(*) as visits
FROM CurrentData
WHERE (((Year(["OrderDate"]))=Year(Now())))
GROUP BY CustID, Year(["OrderDate"]), Val(Format(["OrderDate"],"q"))
ORDER BY Year(["OrderDate"]);