Count Distinct Query in MS Access

shlen

I have the unfortunate task of developing with an Access DB, unfortunately SQL server was not an option.
COUNT DISTINCT(attribute)
is not recognized in Access

I need to build a query that counts only distinct values and then is grouped by 2 values.

I can create a query that counts the distinct values in my table but it only gives me 1 single number I need this number broken up into

Count Location Year

So far this is what I can work with 2 queries, i have tried to write one query but i keep getting errors

I have made one query that pulls the data, as the schema is quite complex and not the most logical(I didn't design it)


SELECT tblAdmission.PatientID, Year([tblAdmission]![DateAdmitted]) AS YearAdmitted, QryHospitalArea.[Hospital Area Name]
FROM (QryHospitalArea INNER JOIN tblHospitalAreaID ON QryHospitalArea.HospitalAreaID = tblHospitalAreaID.HospitalAreaID) INNER JOIN tblAdmission ON tblHospitalAreaID.HospitalAreaID = tblAdmission.HospitalAreaID
GROUP BY tblAdmission.PatientID, Year([tblAdmission]![DateAdmitted]), QryHospitalArea.[Hospital Area Name]
ORDER BY tblAdmission.PatientID, QryHospitalArea.[Hospital Area Name];

SELECT Count(*) AS PatientCount
FROM (SELECT DISTINCT qryCountOfPatientsPerYearByHospital.PatientID FROM qryCountOfPatientsPerYearByHospital) AS PatientCount;

So far this is the only link that I can find online to help,
http://blogs.office.com/b/microsoft-access/archive/2007/09/19/writing-a-count-distinct-query-in-access.aspx

amen

Hmm no MS Access at home but why not create a table with this data aggregrated for each hopital/year based on the query in the MS knowledge base and then use that table in your query ?

Is this work or college ? If work it and I know you are stuck with MS Access it might explain some things about our health system.

btw one small thing I think you results are better as
Location, Year, Count (or maybe year, location) but the count would normally be at the end.