Count(*)

[Don Merchant]

Our organization provides a Supervisory Report to more than 80 school
districts, state wide, annually. Each report must include the districts
contact information, total number of registered students, and the total
number of students by grade. Currently, we generate each district report
using a Microsoft Word Document template we created, which includes a page
designated for each district. However, with over 80 pages in our template,
it’s very time consuming to update the totals portion for each page, each
year.

The following is an example of two column Word Document table with the total
number of registered students and the total number of students by grade that
is listed on each district page.

Col1………Col2
Grade…..# of Students
K5…………1
1st………..1
2nd………0
3rd……….0
4th……….0
5th……….6
6th……….0
7th……….0
8th……….2
9th……….0
10th……..0
11th………0
12th……..0
Total…….10

In addition to the Supervisory Report described above, we also provide an
educational record keeping service to parents and their students, whereas we
provide printouts of report cards, progress reports, transcripts, etc., which
we use Access 2007 as the database for this service.

Since these student reports list the student’s current grade and must be
updated annually, we want to expand our Access database to include a table
containing the contact information from each district in order to merge the
districts contact information, total number of registered students, and the
total number of students by grade into a single page Word Document template.

These are the steps I have taken:

To begin with, the student table contains a field titled [Current Grade]
with the following properties:
Data Type = Text
Display Control = Combo Box
Row Source Type = Value List
Row Source =
"Inactive";"K5";"1st";"2nd";"3rd";"4th";"5th";"6th";"7th";"8th";"9th";"10th";"11th";"12th";"Grad"
Allow Multiple Values = No

Next, I created a district table containing all the fields necessary to
manage their contact information.

Finally, I’m in the process of creating a query titled [Grades by District]
showing the students table and districts table, which will become our source
for merging the districts contact information, total number of registered
students, and the total number of students by grade into our template. What
I’m not sure of is how to do this. I know that I need a totals field for the
following values listed in the row source above:
"K5";"1st";"2nd";"3rd";"4th";"5th";"6th";"7th";"8th";"9th";"10th";"11th";"12th".

Also, I need a total field that counts all students, across all grades,
which would be merged into the totals section mentioned above (Total……..10).

Creating the necessary fields for the contact information using the queries
columns is easy:
Field: District Name (or “Addressâ€, or “Cityâ€, etc.)
Table: Districts
Total: Group By
Sort:
Show: Yes
Criteria:
Or:

My question is what should I enter into the queries columns to generate
fields that provide total number of registered students and the total number
of students by grade?

Thanks in advance for any assistance that can be provided.

Don Merchant

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The total nbr of students per grade per district would be:

Field: District Name (or “Addressâ€, or “Cityâ€, etc.)
Table: Districts
Total: Group By
Sort:
Show: Yes

Field: [Current Grade]
Table: Schools
Total: Group by
Sort:
Show: Yes
Criteria:

Field: StudentID
Table: Schools
Total: Count
Sort:
Show: Yes
Criteria:

The total students per district and the total registered students would
be a products of the report - a cell under the count column that holds
the total count of students:

ControlSource: =Sum(CountOfStudentID)

Two cells will be required: one at the District breaks (when the
district changes on the report) - this will be the count of district
students. The other in the report total section - this will be the
count of the total registered students.

The report could look something like this:

District Name Grade Number of Students
========================== ====== ==================
Berkeley Unified School District K 600
1 500
2 750
... etc. ...

District Total 2,350

Oakland Unified School District K 300
1 500
2 400
... etc. ...

District Total 3,112

Report Total 8,763

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSsaw74echKqOuFEgEQK55ACgkz7+DRNbc7CMgjANXbttdqjcO/gAoP2N
7Ax2O0cjthj6QGwIH+MExkFb
=kDkf
-----END PGP SIGNATURE-----

Our organization provides a Supervisory Report to more than 80 school
districts, state wide, annually. Each report must include the districts
contact information, total number of registered students, and the total
number of students by grade. Currently, we generate each district report
using a Microsoft Word Document template we created, which includes a page
designated for each district. However, with over 80 pages in our template,
it’s very time consuming to update the totals portion for each page, each
year.

The following is an example of two column Word Document table with the total
number of registered students and the total number of students by grade that
is listed on each district page.

Col1………Col2
Grade…..# of Students
K5…………1
1st………..1
2nd………0
3rd……….0
4th……….0
5th……….6
6th……….0
7th……….0
8th……….2
9th……….0
10th……..0
11th………0
12th……..0
Total…….10

In addition to the Supervisory Report described above, we also provide an
educational record keeping service to parents and their students, whereas we
provide printouts of report cards, progress reports, transcripts, etc., which
we use Access 2007 as the database for this service.

Since these student reports list the student’s current grade and must be
updated annually, we want to expand our Access database to include a table
containing the contact information from each district in order to merge the
districts contact information, total number of registered students, and the
total number of students by grade into a single page Word Document template.

These are the steps I have taken:

To begin with, the student table contains a field titled [Current Grade]
with the following properties:
Data Type = Text
Display Control = Combo Box
Row Source Type = Value List
Row Source =
"Inactive";"K5";"1st";"2nd";"3rd";"4th";"5th";"6th";"7th";"8th";"9th";"10th";"11th";"12th";"Grad"
Allow Multiple Values = No

Next, I created a district table containing all the fields necessary to
manage their contact information.

Finally, I’m in the process of creating a query titled [Grades by District]
showing the students table and districts table, which will become our source
for merging the districts contact information, total number of registered
students, and the total number of students by grade into our template. What
I’m not sure of is how to do this. I know that I need a totals field for the
following values listed in the row source above:
"K5";"1st";"2nd";"3rd";"4th";"5th";"6th";"7th";"8th";"9th";"10th";"11th";"12th".

Also, I need a total field that counts all students, across all grades,
which would be merged into the totals section mentioned above (Total……..10).

Creating the necessary fields for the contact information using the queries
columns is easy:
Field: District Name (or “Addressâ€, or “Cityâ€, etc.)
Table: Districts
Total: Group By
Sort:
Show: Yes
Criteria:
Or:

My question is what should I enter into the queries columns to generate
fields that provide total number of registered students and the total number
of students by grade?

Thanks in advance for any assistance that can be provided.

Don Merchant