Need to create input DB for vertical & horizontal reports 1

[Sherman6789]

The following item names and quantities have been reduced in order to make the discussion easier, I hope.

************************************
We have 10 schools:
Sch01 - Sch10

Each school teaches 5 classes only:
Eng, Math, Hist, Gym, Art

Classes are taught 6 months only:
Jan, Mar, May, Jul, Sep, Nov

We need to track the number of students taught in each class by school and month.

Many times the number of students in any given class is zero but the results will show like a spread sheet.
************************************

I have created five tables with equivalant query tables for each of the above. In reality, the number of schools is 33, classes 19, and dates 16. I expected to get about 500 records but when I try to make an input form, I wind up with over 10,000 records. I've set relationships and I've tried mixing query tables.

*************************************
A typical report might like something like this.
Hope the font allows the text to line up properly.
Sample fake data only. The numbers represent quantity of students in each class.

Jan Mar May Jul Sep Nov
Sch-01 Eng 12 30 0 50 3 200
Math 10 12 50 18 0 0
Hist 8 16 0 0 325 8
Gym 60 19 23 50 16 5
Art 80 6 0 35 0 0

Sch-02 Eng 60 12 78 35 25 20
Math 35 67 45 25 55 12
Hist 56 15 84 56 27 90
Gym 78 26 86 35 19 30
Art 70 38 25 92 22 56

Sch-03 Eng 10 8 0 0 7 0
Math 15 18 35 27 0 0
Hist 12 13 0 0 0 0
Gym 30 62 29 25 10 8
Art 25 0 0 0 0 0

Sch-04 Eng 12 30 0 50 3 200
Math 10 12 50 18 0 0
Hist 8 16 0 0 325 8
Gym 60 19 23 50 16 5
Art 80 6 0 35 0 0

Sch-05 Eng 12 30 0 50 3 200
Math 10 12 50 18 0 0
Hist 8 16 0 0 325 8
Gym 60 19 23 50 16 5
Art 80 6 0 35 0 0


Please help with setting up an input form with sub tables, if necessary. Once everything is set up, there will be other reports with vertical and/or horizontal use of the data tables.

Currently, the management is using about 6 separate Excel spread sheets for various reports. This is my attempt to use a database to create all of the reports where data is entered only once. I am now lost.

Thanks for any suggestions.

 

[PHV]

Have a look here:

http://r937.com/relational.html

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886