Setting Up Tables - On Right Track??

[lspelman]

Okay, so I've been reading information on normalization and entity-relationship diagramming from the links some of you shared with me yesterday. I'm not sure if I'm making this more difficult than it has to be or what but feel like I'm going in circles trying to get this figured out . . .

I've have written it out to try and make sense of it and hopefully get some help . . .

There are many consultants that can work with more than one student per day, providing more than one service in that day for each student.
Each student has one orign, language and belongs to one school.

Each school has one division and many students

Each service can be done more than once in a day, for more than one student

Some services have many descriptions.

I'm thinking I need tables like the following:

Consultant Table Student Table Service Table
Consultant ID Student ID Service ID
Consultant First N Student First N Service Name
Consultant Last N Student Last N Service Desc (many)
Origin ID
Language ID
School ID


School Table Origin Table Language Table
School ID Origin ID Language ID
School Name Origin Name Language Name
School Division

Am I on the right track here or am I creating too many tables?



Also, I'm not sure how to deal with the fact that only some of the services have many descriptions not all of them. Do I need another table for that.

And for entering the data, it seems like each consultant is going to have enter duplicate info ie: Repetitive dates

Once again, any help will be appreciated.

Thank goodness for this forum!

Leanne

[lspelman]

Oh shoot . . .

I can see my tables are all screwed up. I guess it is obvious that I'm new here! Sorry. I will try again. . .

Consultant Table
Consultant ID
Consultant First N
Consultant Last N

Student Table
Student ID
Student First N
Student Last N
Origin ID
Language ID
School ID

Service Table
Service ID
Service Name
Service Desc (many for some services)

School Table
School ID
School Name
School Division

Origin Table
Origin ID
Origin Name

Language Table
Language ID
Language Name

[RuralGuy]

Now you are on the right track. Way to go.

[lspelman]

Ok thanks but Im still needing help . . . !!!

Ultimately I need to show have a report by consultant, showing how many hours they worked in one month, what schools they were at, what languages and how many times, and how many times for each of the services.

Is that even possible? What tables/relationships am I missing to make this happen?

Would I be better to start from scratch or is there a template anyone can recommend that is similiar that I can tailor?

Thanks,
Leanne

[RuralGuy]

If I were you I would start playing with the Query Builder and see if you can create a query with all of the details you need for your report. Be sure and use the Relationship menu to define your relationships first which will define the Joins for you.