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
GROUP BY AppliedCensusBlocks.ApplicantNumber, AppliedCensusBlocks.Region) AS myQRY
GROUP BY myQry.ApplicantNumber, myQry.Region
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 );