Access Query: Unable to get correct Count

Question

I have a table named [Mailbox Status] listing all email mailboxes that are to be migrated to O365. Mailboxes are migrated in batches (column BatchNumber) and when they are migrated, the column MailboxState is set to 'Client Migrated'.

For each BatchNumber, I need to count the number of migrated mailboxes (MailboxState = 'Client Migrated').

SELECT [Mailbox Status].BatchNumber,
       DCount("MailboxState","[Mailbox Status]", "[MailboxState]='Client Migrated'") As MigratedCount
FROM [Mailbox Status]
WHERE [Mailbox Status].BatchNumber is not NULL
GROUP BY [Mailbox Status].BatchNumber ;

The above query is returning the count of migrated mailboxes across all BatchNumbers instead of returning the count for a given BatchNumber.

What am I doing wrong?

Answer 1

If I understand correctly, you could use:

SELECT ms.BatchNumber, COUNT(*) As MigratedCount
FROM [Mailbox Status] as ms
WHERE ms.BatchNumber is not NULL AND
      ms.MailboxState = 'Client Migrated'
GROUP BY ms.BatchNumber ;

This will filter out any batches that have no migrations. If you need those as well, use conditional aggregation:

SELECT ms.BatchNumber,
       SUM(IIF(ms.MailboxState = 'Client Migrated', 1, 0)) As MigratedCount
FROM [Mailbox Status] as ms
WHERE ms.BatchNumber is not NULL 
GROUP BY ms.BatchNumber ;

DCOUNT() and COUNT() are quite different. DCOUNT() counts records across all rows in a result set. COUNT() is the aggregation function for determining the number of rows summarized on one row in an aggregation query.