Need Report Help Comparing Data

[patriciashoe]

I have a database that features a number of numeric fields compiled during a year. Example: For each of 13 schools there is one record for each year. How do I construct a report that compares this one record for each school against the the record for the previous year? I would also like the report to show data from each year and have a number of calculated fields showing the difference between the two records (or years). I am basically trying to construct a report that was previously based on an excel spreadsheet. Thanks for getting me pointed in the right direction.

Patti

[MMcCarthy]

Patricia we would need more information to help.

Firstly how is the year identified in the record. Secondly can you give an example of some of the data differences you would like to highlight. You will also need to provide the metadata for the table holding this record. Is it a table?

Here is an example of how to post table MetaData :
[b]Table Name=tblStudent[/]

Field; Type; IndexInfo
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

[patriciashoe]

Thanks for the reply. The data is currently stored in a table.There is a text field used to indicate the date, for example, 2007-2008. The metadata is noted below. Basically I record the number of teachers in each area per school. What I want to compare is the number of teachers is one area say schools A's number of art teachers in year 2007-2008 to what is projected for year 2008-2009. This could be an increase or decrease depending. Initially, I want to develop a report for each school making these comparisons. The field names with FTE in them needs to be compared against last years' data.

Patti

Table Fields and Data Types

 
elementary_data

       Field Name                                Field Type                  Size   Required     Validation
          1enroll                                    Number (Long               4
          1fte                                       Number (Long               4

          2enroll                                    Number (Long               4
          2fte                                       Number (Long               4
          3enroll                                    Number (Long               4

          3fte                                       Number (Long               4
          4co                                        Number (Long               4
          4enroll                                    Number (Long               4

          4fte                                       Number (Long               4
          5co                                        Number (Long               4
          5enroll                                    Number (Long               4

          5fte                                       Number (Long               4
          6enroll                                    Number (Long               4
          6fte                                       Number (Long               4

          avclsz1                                    Number (Long               4
          avclsz2                                    Number (Long               4
          avclsz3                                    Number (Long               4

          avclsz4                                    Number (Long               4
          avclsz5                                    Number (Long               4
          avclszk                                    Number (Long               4

          enrollaisMath                              Text                      50
          enrollAISRead                              Text                      50
          enrollAISReadRcv                           Text                      50

          enrollart                                  Text                      50
          enrollcounselor                            Text                      50
          enrollG/T                                  Text                      50

          enrollLibrary                              Text                      50
          enrollMusic                                Text                      50
          enrollPhysed                               Text                      50

          enrollpsychologist                         Text                      50
          enrollse121                                Number (Long               4
          enrollse151                                Number (Long               4

          enrollse611                                Number (Long               4
          enrollse811                                Number (Long               4
          enrollSocialWorker                         Text                      50

          enrolserrct                                Number (Long               4
          FTEAIS Math                                Number (Long               4
          FTEAIS Read                                Number (Long               4

          FTEAIS Read Rcv                            Number (Long               4
          FTEArt                                     Number (Long               4
          FTEcounselor                               Number (Long               4

          FTEG/T                                     Number (Long               4
          FTElibrary                                 Number (Long               4
          FTEMusic                                   Number (Long               4

          FTEPhysed                                  Number (Long               4
          FTEpsycologist                             Number (Long               4
          ftese121                                   Number (Long               4

          ftese151                                   Number (Long               4
          ftese6111                                  Number (Long               4
          ftese811                                   Number (Long               4

          fteserrct                                  Number (Long               4
          FTESocial Worker                           Number (Long               4
          ID                                         Counter                    4

          kenroll                                    Number (Long               4
          kFTE                                       Number (Long               4
          Notes                                      Text                      255
          PreKEnroll                                 Number (Long               4

          prekFTE                                    Number (Long               4
          school_id                                  Number (Long               4

          totgenenroll                               Number (Long               4
          totscenroll                                Number (Long               4
          year                                       Text                      50

[MMcCarthy]

It looks like your data is not normalises. At a guess I would say you just imported the excel spreadsheet. You will need to normalise this into tables if you want to query and report the data.

Have a look at this tutorial on Database Normalization and Table structures

[patriciashoe]

Actually it is. There is one table that holds all the school identification data. The table I listed in the previous post is all the data for a given year that is related to the above table. The structure is as follows:

Table SchoolId holds the name and an id number of each school.

The schooldata table is linked to this table via the school id.

I have been working with Access for a number of years and have done some fairly sophisticated applications. The report requirements for this one however eludes me at the moment. Want I want for each school is this year's data printed on the same page as last years data. This would involve two records for each school. Let me know what you think with this in mind Would a query with the comparison calculations in the query do they trick? Thanks,

Patti

[MMcCarthy]

Actually it is. There is one table that holds all the school identification data. The table I listed in the previous post is all the data for a given year that is related to the above table. The structure is as follows:

Table SchoolId holds the name and an id number of each school.

The schooldata table is linked to this table via the school id.

I have been working with Access for a number of years and have done some fairly sophisticated applications. The report requirements for this one however eludes me at the moment. Want I want for each school is this year's data printed on the same page as last years data. This would involve two records for each school. Let me know what you think with this in mind Would a query with the comparison calculations in the query do they trick? Thanks,

Patti

Then why have you got enroll1, enroll2, etc. and enrollsubjectna me. This table really doesn't look normalised and a good rule of thumb is that there are so many fields in the table. This is very unusual in a normalised table.

[patriciashoe]

The fields in this table represent the number of teachers in a grade area (the fte fields) and the enrollment for each grade. 1enroll being first grade and 2enroll second grade and so on. These schools range from kindergarten to 5th grade. The data I have in this table is the number of teachers and enrollment for each grade and subject area. I also have tables set up for middle and high schools. With this in mind, do you recommend another structure? Thanks,

Patti

[MMcCarthy]

In simplified terms, this is the kind of thing I mean.

tblTeacher
TeacherID (Primary Key)
TeacherName
GradeID (Foreign Key Referencing tblGrade)
SubjectID (Foreign Key Referencing tblSubject)

tblGrade
GradeID (Primary Key)
GradeName

tblSubject
SubjectID (Primary Key)
SubjectName

tblEnroll
EnrollID (Primary Key)
EnrollDate
StudentID (Foreign Key Referencing tblStudent)
GradeID (Foreign Key Referencing tblGrade)
SubjectID (Foreign Key Referencing tblSubject)

In this simplified version a teacher teaches a subject in a grade. If a teacher teaches more than one subject and/or more than one grade its a little more complicated.

Then students enroll in a grade and subject. If students enroll in more than one subject then you create a new record for each subject.

This may not exactly meet your needs but you can see what I mean by normalising the data. This makes it much easier to query and report.

[patriciashoe]

Your simplified version in the previous post is close to what I need, This is the data I need to store.

A list of 13 schools –

For each school there are grade levels, i.e. first grade and so on.

For each school there are enrollments for each grade.

Example

School A for year 2007-8
#First Grade teachers 5 Student enrollment 1st 125
#Second Grade teachers 3 Student enrollment 2nd grade 75

And so on.

Teacher numbers are also categorized by subject area.

Example

School A
#Art Teachers 1 Student enrollment Art 130

I need some assistance in normalizing this data. Each school has a number of grade levels and subject as well as enrollments connected to each grade level and subject.

What I need to compare is the number of teacher at each grade level per school from one to the next. I don;t need to identify students or teachers just store totals for each grade and enrollment.

Example:

School A
Year 2007-8
First grade Teachers 5

Year 2008-9
First Grade Teachers 6

I need to calculate that first grade teachers in this building increased by 1

Thanks for your help. It is becoming clearer.

Patti