asked on

MS Access version of Excel's CountIf

Dear Experts,

I'm doing some de-duplication work on a membership database and, having exported the results I have created a new table "Post123WithGradeAChecks". This table contains various fields including the GUID of the original record and the field I wish to query [NameAndCo].

An example would be:

[GUID] [NameAndCo]
123 JohnSmithApple
456 JoeSpenceMicrosoft
789 PeterWinApple
643 JohnSmithApple

I need to create a query which counts how many times NameAndCo appears in the table, using the example above the results of this query would be:

[GUID] [NameAndCo] [results]
123 JohnSmithApple 2
456 JoeSpenceMicrosoft 1
789 PeterWinApple 1
643 JohnSmithApple 2

The data set is quite large, otherwise I would do this in excel using a countif formula

Can anybody help?

ste5an

Use the Sigma icon in the query designer to great a Totals query. As SQL:

SELECT  [NameAndCo] ,
        COUNT(*) AS Cnt
FROM    YourTable
GROUP BY [NameAndCo];

Open in new window

Ryan Chong

if you want to use CountIF formula in Excel, you can try this.
28711853.xlsx

Tom Crowfoot

ASKER

Hi ste5an

Thanks for this - it works, but only if I don't include any other fields from the record (i.e. the GUID) which I need to include as that's the link back to the original record.

Any ideas?

Many thanks

Ryan Chong

try

SELECT  a.[GUID], a.[NameAndCo] , b.cnt
FROM    YourTable a inner join (select [NameAndCo], count(*) cnt from YourTable group by [NameAndCo]) b
on a.[NameAndCo] = b.[NameAndCo]
order by a.[GUID]

Open in new window

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tom Crowfoot

ASKER

perfect, works a treat - thank you very much