Convert Excel Formula to a Access query (1 Viewer)

Number11

Member
Local time
Today, 13:08
Joined
Jan 29, 2020
Messages
607
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 CountCandidate Total Count
=1/COUNTIFS(A:A,A2,F:F,F2)​
=1/COUNTIF(A:A,A2)​
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:08
Joined
Oct 29, 2018
Messages
21,357
Hmm, have you tried DCount()? Just curious...
 

Number11

Member
Local time
Today, 13:08
Joined
Jan 29, 2020
Messages
607
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

Banishment Pending
Local time
Today, 08:08
Joined
May 11, 2011
Messages
11,611
For an Access query it would be a simple aggregate query:


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

theDBguy

I’m here to help
Staff member
Local time
Today, 06:08
Joined
Oct 29, 2018
Messages
21,357
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.
Code:
=DCount("*","TableName","AccountNo='123456' AND WeekNo=52")
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:08
Joined
Feb 19, 2002
Messages
42,970
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

..forever waiting... waiting for jellybean!
Local time
Today, 21:08
Joined
May 7, 2009
Messages
19,169
SELECT Table1.AccountNo, Count(Table1.[Week No]) AS [CountOfWeek No]
FROM Table1
GROUP BY Table1.AccountNo;
 

Users who are viewing this thread

Top Bottom