Attendance Count

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using Access 2003. I have a Students table with fields for Admission
Date and Withdrawal date. If the student is still attending the Withdrawal
Date is null. I would like to make a report that would tell me how many
students were enrolled during each month. I made a query that would give me
any individual month (for example March of this year)

Admission Date <4/1/05 and Withdrawal Date Is Null or >=3/1/05

But I would like a report that would just list the months and say how many
students were enrolled.

i.e.

January 2004 56
February 2004 62
March 2004 70
etc, etc.

I apologize if this question would be better placed in "queries".

Adam
 
This is more of a queries question but not to worry...

Consider creating a table of all beginning month dates in your range:

tblMonths
=============
MthStart
1/1/2005
2/1/2005
3/1/2005
....
12/1/2008

You can then add this table to a query with your students table. Don't
create any join lines. Set the criteria like
Format(MthStart,"yyyymm") Between Format([Admission Date],"yyyymm") and
Format([Withdrawal Date]), "yyyymm")

Make the query into a totals query and add the MthStart to the grid and
StudentID. Group By MthStart and Count StudentID.
 
Hey thanks. It's funny, I am always annoyed when one of my query lines is
missing and I get like 40000 records in a query that should only have 400. I
never even thought there could be a use for that feature. I learn something
every day.

Duane Hookom said:
This is more of a queries question but not to worry...

Consider creating a table of all beginning month dates in your range:

tblMonths
=============
MthStart
1/1/2005
2/1/2005
3/1/2005
....
12/1/2008

You can then add this table to a query with your students table. Don't
create any join lines. Set the criteria like
Format(MthStart,"yyyymm") Between Format([Admission Date],"yyyymm") and
Format([Withdrawal Date]), "yyyymm")

Make the query into a totals query and add the MthStart to the grid and
StudentID. Group By MthStart and Count StudentID.

--
Duane Hookom
MS Access MVP


bumpmobile said:
I am using Access 2003. I have a Students table with fields for Admission
Date and Withdrawal date. If the student is still attending the
Withdrawal
Date is null. I would like to make a report that would tell me how many
students were enrolled during each month. I made a query that would give
me
any individual month (for example March of this year)

Admission Date <4/1/05 and Withdrawal Date Is Null or >=3/1/05

But I would like a report that would just list the months and say how many
students were enrolled.

i.e.

January 2004 56
February 2004 62
March 2004 70
etc, etc.

I apologize if this question would be better placed in "queries".

Adam
Click to expand...
Click to expand...
 
Back
Top