Count applicants

[UPS_HPA]

Greetings.

My database tracks applicants to certain programs, showing which year they applied, which program they applied to, whether they got in, and so on. Each person has a unique ID number. Each person may apply to multiple programs, and may apply in multiple years.

I have a query that shows me how many programs a person applied to in a year. What I need to do now is to count the number of people who have applied each year.

Is there a way to do this?

Thanks in advance for any help you can offer!

UPS_HPA

 

[aleb]

SELECT Format([yourdatefield],"yyyy") AS [Year], Count(yourtable.yourapplicantname) AS CountOfApplicant, yourtable.yourapplicantname
FROM yourtable
GROUP BY Format([yourdatefield],"yyyy"), yourtable.yourapplicantname;

this query counts how many times applicant applied for courses within the year

SELECT Format([yourdatefield],"yyyy") AS [Year], Count(yourtable.yourapplicantname) AS CountOfApplicant
FROM yourtable
GROUP BY Format([yourdatefield],"yyyy");


This query counts how many applications were within the year.

 

[UPS_HPA]

Okay,

At the risk of sounding even dumber than Access usually makes me feel, how do I implement your elegant-looking solution?

I can concoct a query in "Design" view; I don't know how to do it any other way.

Thanks in advance,

UPS_HPA

 

[UPS_HPA]

I finally figured out how to plug the code in to a query so I could see what it came up with.

Alas, I already have a query that counts applications per applicant. I also have a query that counts total applications for the year. (I like the option to enter the name I am looking for though - need to explore that for another part of this project)

Is it possible to get a total of applicants for a year? What I think I need to do is group by year, then count the unique occurrences of the applicant's ID number within that year. Unfortunately, I can't figure out how to do it.

Any help towards a solution would be appreciated!
Thanks in advance,

UPS_HPA

 

[aleb]

It is possible if you do it in two steps first you will create a query that will group the year and the applicant name

SELECT Format([yourdatefield],"yyyy") AS [Year], (yourtable.yourapplicantname) AS [Name],
FROM yourtable
GROUP BY Format([yourdatefield],"yyyy"), yourtable.yourapplicantname;

second query will be

SELECT Year, Count(query1.name) AS CountOfname,
FROM query1
GROUP BY query1.year;