Convert Excel Formula to a Access query

[Number11]

Need to find away for access to provide the same results as an excel formula I have for counting the number of times a customer appears in the same week number

Column A = AccountNo
Column F - Week Number

Candidate Week Count	Candidate Total Count
=1/COUNTIFS(A:A,A2,F:F,F2)​	​ =1/COUNTIF(A:A,A2)​

 

[theDBguy]

Hmm, have you tried DCount()? Just curious...

 

[Number11]

Hmm, have you tried DCount()? Just curious...

Thanks and oh no haven't tried that and not 100% sure how that would look in regards to formula

 

[plog]

For an Access query it would be a simple aggregate query:

Count data by using a query - Microsoft Support

Understand ways to count data, count data by using a Total row or by using a totals query, and view the Aggregate function reference in Access.

support.microsoft.com

SELECT F, COUNT(F) AS Appearances 
FROM YourTableNameHere
WHERE A='Enter Specific Account Number Here'
GROUP BY F

 

[theDBguy]

Thanks and oh no haven't tried that and not 100% sure how that would look in regards to formula

My guess would be something along these lines.

=DCount("*","TableName","AccountNo='123456' AND WeekNo=52")

 

[Pat Hartman]

I wouldn't use a domain function. I would use the totals query suggested by plog. Domain functions are extremely inefficient and should not be used in queries or inside VBA loops if there are other alternatives.

 

[arnelgp]

SELECT Table1.AccountNo, Count(Table1.[Week No]) AS [CountOfWeek No]
FROM Table1
GROUP BY Table1.AccountNo;