Question about counting records with query

[david7979]

Hello everyone! I'm new to this forum and this forum has been a very useful resource on creating my first database. I know nothing about coding and this is my first time trying to create an Access database for work. I have been working on this database for more than 4 months and this forum really helps me from creating tables all the way up to forms. THANK YOU!!

Now, I'm struggling on creating reports that counts records from my tables. Here are two of my problems:

First:

I have a main table called "tbl_main" which records students' basic information, including their birthday in a column called [dob] in mm/dd/yyyy format. I need to create a query that counts how many students in age 11, 12, 13, 14, 15,16, and 17 separately so I can list them on a report.

Second:

I have a enrollment history table called "tbl_enrollment" which records dates that students had been in and out of the school. Since a student can re-enroll for more than once, there are more than one record for the same student. I have student ID [idnumber] and admitted date [admitted] columns in this table. I need to create a query that gives me a total number of enrollment, number of one-timer, and number of repeater, so I can list these numbers on a report.

For both reports above, user will input a timeframe to count particular number of records. Thanks to this forum, I already have a working form with start/end fields for user to input. What I'm struggling is creating queries to count records.

Thanks again for spending time reading my first post in this forum and I hope I can get helps from anyone of you. Since I'm not a programer, you may need to step-by-step walk me through the whole process. Sorry!!

 

[ColinEssex]

For your first question, do a search for "calculate age" or something. There's plenty of examples on how to do this. Then do a count query.

Your second, you need to create another count query with the main table and the enrollment table. Pull in the fields you need and do a count on the StudentID. For the date range, add the enrollment date to the query and untick the "show" box as you don't need this in the output but you need it in the calculation. An example of the date range syntax is

Between #01/08/05# and #17/08/05#

Col