Access query: statistics for single year and more than single year

[iren]

Hi all,
I have Adm_key ; Adm_mem_num fields and process_year.

Adm_key adm_mem_num Process_year

8 12 2005
8 12 2005

9 11 2005
9 11 2004

7 10 2005
7 10 2004
7 10 2003


I need to know how many claims are in a single process year and how many in more than single year. Something like the following statistics based on the file above

Just 2 admissions have claims in a single year (2005)
2 of them have claims in 2 different years (2005 & 2004)
3 of them have claims in more than 2 different years (2005 & 2004& 2003)

How can I implement it in Access?

Thank you in advance,

Iren

 

[rac2]

Maybe something that we might call a nested GROUP BY, that is, a GROUP BY query on another GROUP BY.

This will give one row for each year for each Adm_key key

SELECT Adm_key, Process_year, COUNT(*) AS "ClaimsPerYear"
FROM MyTable
GROUP BY Adm_key, Process_year

This would also give that result.

SELECT DISTINCT Adm_key, Process_year
FROM MyTable

Either one of these could be saved as a SELECT query, call it AdmissionYearClaims

Here are the results for AdmissionYearClaims
Adm_key Process_year ClaimsPerYear

8 2005 2
9 2005 1
9 2004 1
7 2005 1
7 2004 1
7 2003 1


The next query will give the number of years for each Adm_key. Save this one and call it YearsProcessed.

SELECT Adm_key, 
       SUM(ClaimsPerYear) AS "N_Claims", 
       COUNT(*) AS "N_Years"
FROM AdmissionYears
GROUP BY Adm_key

Here are the results for YearsProcessed
Adm_key N_Claims N_Years

8 2 1
9 2 2
7 3 3



Finally the result you describe

SELECT N_Years, 
       SUM(N_Claims) AS "Claims"
FROM YearsProcessed
GROUP BY N_Years

Should yield
Claims N_Years

2 1
2 2
3 3