Is my Acess database properly mormalized to develop the report I need?

I need some help with data normalization.  I have some years of experience with Access but this one eludes me.  Basically, I am taking data from an Excel spreadsheet and converting onto an Access database.  In Excel each of thirteen public schools has its own page that records that number of teacher at each grade level and by subject along with enrollment.  For example. At the elemtary level I have data for the following:

Grades 1 through five = # of teachers
Grades 1 through 5 # of students enrolled
Special Subject area teachers # of
Special Subject areas # of student enrolled.

This data is organized by year.  I need to eventually produce a report that compares the change in the above data from one year to the next.  In other words, do I have more or less first grade teachers  that the year before..

Currently I have table that identifies the school and assigns a unique id.  I have a table for elementary schools that has a filed for every one of the above i.e.

Grade 1 teachers #
Grade 2 teachers #  and so on
Also
Grade 1 enrollment
Grade 2 enrollment and so on.  

I have a form that open a record based on the school and a subform that collects all the above per school.

I dont know if this is normalized as well as it should be.  I am also somewhat unsure about how to develop the report I am looking for.  I just need a push in the right direction.
SbovinoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nmcdermaidCommented:
So in a single record you have these fields:

Grade 1 teachers,  Grade 2  teachers, Grade 3 teachers etc...

I really wouldn't suggest that. Repeating columns breaks one of the normalisation rules (I don't know which I just know its bad!). I suggest looking at all the 'objects' that you are modelling, creating a table for each object, and testing that design.


For example the objects I see here are:

Schools
GradeLevels
Teachers (are you interested in individual teacher attributes about teachers, i.e. name, age?)
Subjects


Then have a think about all the attributes that you can assign to each of those objects.
-A particular subject may be expected to only teach at certain grade levels
-A particular teacher may have some other reporting element attached to them like age group or gender



Then think about the cardinality:
-A teacher may only teach certain subjects
-A teacher probably only teaches at one school (but if it is remotely concievably that they can teach at more than one then don't limit it)


Then think about any linking tables. Particuarly one that takes a snapshot of the student counts each your, and links all the tables up to describe it:


tblEnrolment
YearNumber         <- This could be month number
SchoolID               <- FK to tblSchools
GradeID                <- FK to Grades
TeacherID             <- FK to Teachers
SubjectID              <- FK to subjects
StudentsEnrolled  <- Count of students enrolled in a particular year, school,grade,subject,taught by a certain teacher


This table allows you to take a yearly (or monthly) snapshot of enrolments.

You can do any summary along any particular lines for this table, i.e. the number of students enrolled in subject 1 in grade 3 over time:

SELECT YearEnrolled, SUM(StudentsEnrolled)
FROM tblEnrolment
WHERE SubjectID = 1
AND GradeID = 3
GROUP BY YearEnrolled



HOWEVER if a student can be enrolled in two subjects in the same year/teacher then this can double count the students.

If you were to change this table to include a StudentID, then you could also get around that double counting issue.





Don't let the Excel representation cloud your modelling. Thats how it should look at the end, rather then how it should be modelled.

Let me know any feedback.
0
SbovinoAuthor Commented:
Thanks for the reply.  Tonight I will try to set up the structure.  One comment however.  This database does not need to get to the level of identifying teachers or students.  It is designed to do a count of teachers in each grade and subject area along with the number of student enrolled.  For example, at the elementary level I only need report (for each building) how many teachers are teaching first grade and how many students are enrolled.  No teachers should be double counted.  The requirements are not as robust as your post.  The tracking is by year.  In additional to grade levels, I have to track the similar data but by subject area.  For example, at the high school how many teachers teach English and how many students are enrolled in English.  If my ramblings suggest any change to your suggested structure let me know.  Thanks,

Steve
0
nmcdermaidCommented:
>> count of teachers in each grade and subject area along with the number of student enrolled. No teachers should be double counted

I'll just ask you some quesions to clarify some things.... we are making progress but I'll ask questons first.

First question(s): Can a teacher teach two subjects in one grade? Can a student attend two subjects in one grade?

If we had a table like this (not the final table, just an example):

Year  SchoolName  GradeName SubjectName TeacherCount StudentCount
2006    School1             8                English             4                   30
2006    School1             8                Maths               5                   50
2006    School1             8                Art                    1                   20
2006    School1             8                Music               2                   28
2006    School1             9                English             4                   50
.....................
...
..

Of the 30 students attending grade 8 english, how many of those are ALSO attending Grade 8 maths? Lets say all 30 are.

When you look at the individual record, its correct. However if you asked 'how many unique students attend year 8 at School 1'. there's no way to know from this table. If you add it all up you get 128, but really there's probably only 50.

Same goes for teachers. The table design does not allow you to count unique teachers.

So.. is this a problem??? if its not then all we have to do is break the school, grade and subject out into individual tables.


>> In additional to grade levels, I have to track the similar data but by subject area.  For example, at the high school how many teachers teach English and how many students are enrolled in English

If you get your base table(s) right, then this is just a matter of writing the correct query. The table above WILL satisfy that question to a certain extent, but if a teacher teaches grade 8 AND grade 9 English they will be counted twice.


Right now the only way I can think of to get around that double counting is have a unique teacher ID, then the query is easy.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

SbovinoAuthor Commented:
We are getting very close.  I have attached a spreadsheet that we were using to illustrate the following point.  This database is not used to track and calculate overall enrollment or individual teachers or individual students.  The issue of double counting students is a non issue.  What the database does track is the number of teachers in each building.  Teachers will not be double counted by design.  Teachers are either assigned to a grade or subject as expressed as a Full Time Equivalent (education jargon). This application does not track the number of classes that a teacher may teach.   For example, in the list below, there are 5 full time 1st grade teachers.  That is what is tracked.  The fact that they teach six periods a day is not tracked.  Their individual students are not tracked.  This type information is tracked in another system.  This application is designed to track aggregate staffing needs.  

This is an example from one building.
1st grade 5
2nd grade 5
3rd grade 4
4th grade 4
5th grade 5
Art 2
Music 2
Phys Ed 2
Computer 2
Special education 4.5

This building then has 35.5 FTE teachers.  No teacher numbers are duplicated.  The process is essentially the same for other buildings.  When you get to middle and high school the classifications are by subject as opposed to grade levels.  It is possible that a teacher might teach the equivalent of fulltime which is expressed as 1.0 FTE however, their assignment is .5 math and .5 science.  We account for that in the FTE number.  We don't need to know the identity of the teacher.  I believe the task is somewhat easier that you anticipated.

What I need to produce initially is something a kin to the attached spreadsheet.  I have to compare for each building the difference in staffing levels from one year to the next.  We only track in this database by school year.

 Let me know if I have explained this adequately.  Thanks for the help.

Steve


sample-elementary.xls
0
nmcdermaidCommented:
Just a quick comment on this:

>> This type information is tracked in another system.  This application is designed to track aggregate staffing needs.  

Can you just extract and summarise the info you need from this system, or is it closed/missing detail?


Anyway thats enough of me complicating the issue.

The FTE thing wraps it up nicely.

Here is a table ~before~ it has foreign key tables split out from it:

Year  BuildingName  GradeSubjectName TeacherFTE StudentCount
2006    Building1             Year 1                           2                   60
2006    Building1             Year 2                           3                   80
2006    Building2             Year 3                           2                   60
2006    Building2             Year 4                           3                   70
....
..
..
2006    Building4             Year 8 Maths                2                   70
2006    Building4             Year 8 English              2                   70
2006    Building4             Year 8 Art                     2                   70
...
..
.

Possibly it does most of what you need, but have a think about the following points:

-Everything except the FTE and StudentCount (and maybe the Year) will need to be broken out to another table
-Are you tracking the building just so you know which school its at? Or is there another reason (capacity?) to track it?
-Do you care about the difference between Year 8 Maths and Year 9 Maths, or is it just 'Secondary Maths'



If the table is pretty close then the next step would be to break it out properly:



tblResourcing:

Year  BuildingID  GradeSubjectID TeacherFTE StudentCount
2006    1                       1                           2                   60


tblBuilding:

BuildingID BuildingName SchoolID
    1             A Block              1


tblSchool:

SchoolID SchoolName
   1             A School Somewhere


tblGradeSubject

GradeSubjectID GradeSubjectName
          1                      Year 1
          2                      Year 2
....
..
.
     12                        Year 8 Art
     13                        Year 8 English


In this data model, the building that you select (A Block) automatically selects a school. If your buildings are more generic then you can move SchoolId straight into tblResourcing


I don't think this is complete at this stage but its a starting design that may highlight any drawbacks before we go any further.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SbovinoAuthor Commented:
Are you tracking the building just so you know which school its at? Or is there another reason (capacity?) to track it?
-Do you care about the difference between Year 8 Maths and Year 9 Maths, or is it just 'Secondary Maths'

TO your first question - this data cannot be extracted from the other system at this time.

Yes, I need to track this by building so I can produce a report for each building.  I am using the term building and school interchangeably.  In the secondary schools we track by subject i.e. Math which includes all levels.  I will start implementing the design.  I still need some guidance on how I would compare FTEs per building from one year to the next.  Thanks again,

Steve
0
SbovinoAuthor Commented:
THis structure worked very well.  I am going to post some additional questions on reporting and filtering.  I hope to hear from you again.  Thanks,

Steve
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.