Extract, count and report from multiple queries

[kfox]

I have set up a table for statistical information of students from our 8 campuses (campus, student first name last name, dob, parent info, grade, etc.). I have a query set-up for each campus.


I also have an excel spreadsheet when a student enrolls I change the number of students from each campus that are in grades 1-3, 4-6, 7-8 and 9-12.

Since I have already entered this information into my Access DB, I am wondering why do double-duty (thereby risking my forgetfulness) when I should be able to extract that information from these queries? Not sure how to do this in this grouping, per campus, giving a total count for each campus. Any help would be appreciated.

[rpeare]

Why are you maintaining the excel spreadsheet at all, why not just have a form or report that shows the information when you need it? I may be misunderstanding but it seems like you want to export that information to excel which I don't see a reason for based on your requirements.

Yes you can do what you want

What you want is a summation query let's say you have the table

tblStudents
StudentID  CampusID, Grade

Where StudentID is an autonumber PK
CampusID is a number (foreign key to your campus list table that has an autonumber PK as well)
Grade is a number value of the grade the child is in this year

this would be your query

SELECT tblStudents.CampusID, IIf([grade] Between 1 And 3,"1-3",IIf([grade] Between 4 And 6,"4-6",IIf([grade] Between 7 And 8,"7 -8",IIf([grade] Between 9 And 12,"9 - 12","XX")))) AS GradeLabel, Count(tblStudents.StudentID) AS [Student Count]
FROM tblStudents
GROUP BY tblStudents.CampusID, IIf([grade] Between 1 And 3,"1-3",IIf([grade] Between 4 And 6,"4-6",IIf([grade] Between 7 And 8,"7 -8",IIf([grade] Between 9 And 12,"9 - 12","XX"))));

Make a dummy table and look at how it's constructed then you can adapt it to your situation.