CODEWITHSUNDEEP
sqlms-access
gfunkdave
gfunkdave

Reputation: 73

How can I get Access SQL to return a dataset of the largest value in each category?

This has been driving me crazy all day, and I've gone through every solution I can find on here. This should be a very simple thing.

I have a table in Access that contains a list of applications:

ApplicantNumber | Region

There are many more columns, but those are the two I care about at the moment. Each row is a separate application, and each applicant can submit multiple applications.

I have a query in Access that finds the count per applicant of applications in each region:

ApplicantNumber | Region | CountOfAPplications

How the #@&*!!! do I pull out of that the region with the most applications for each ApplicantNumber?

As far as I can tell, the following should work fine but it just provides the same output as the initial query with the full count per applicant:

SELECT myQry.ApplicantNumber, myQRY.Region, Max(myQRY.CountOfRegion)
FROM (SELECT AppliedCensusBlocks.ApplicantNumber, AppliedCensusBlocks.Region, Count(AppliedCensusBlocks.Region) AS CountOfRegion
FROM AppliedCensusBlocks
GROUP BY AppliedCensusBlocks.ApplicantNumber, AppliedCensusBlocks.Region)  AS myQRY
GROUP BY myQry.ApplicantNumber, myQry.Region

What am I doing wrong? If I remove the Region field, Access will work as I'd expect and just show the ApplicantNumber and maximum count. BUt I'm really trying to get at the region name associated with the maximum count.

Upvotes: 0

Views: 38

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270513

This is a bit tricky. MS Access is not the best suited for this sort of query. But here is one way

  SELECT acb.ApplicantNumber, acb.Region, Count(*) AS CountOfRegion
  FROM AppliedCensusBlocks as acb
  GROUP BY acb.ApplicantNumber, acb.Region
  HAVING COUNT(*) = (SELECT TOP 1 COUNT(*)
                     FROM AppliedCensusBlocks as acb2
                     WHERE acb2.ApplicantNumber = acb.ApplicantNumber
                     GROUP BY acb2.Region
                     ORDER BY COUNT(*) DESC, acb2.Region
                    );

Upvotes: 1

Chriz
Chriz

Reputation: 597

SELECT TOP 1 ApplicantNumber, Region, COUNT(*) AS Applications
FROM AppliedCensusBlocks
GROUP BY ApplicantNumber, Region
ORDER BY COUNT(*) DESC

Upvotes: 0

Related Questions