contacts database

[cuttsy]

I need to make a database of contacts from schools to be used as a mailinglist to advertise educational events. I can only think of one way to do it but i have a feeling someone will tell me that this is wrong.

tblSchool (holds the name address and a few other details)

tblContacts (holds names and shcool worked at and then several yes/no fields showing their positions at the school such as Head Teacher, Head of English, Careers Advisor)

If we had an event to avertise that was for students on computing or media courses at college/high school then i would run a query for staff at schools and colleges that have head of media OR head of computing ticked.

Like I said I don't know if this is an advisable route to take but its the only way that I can think to set it up. If this is indeed wrong could someone please tell me so and suggest a better solution. thanks.

 

[Mile-O]

tblSchools
SchoolID (Autonumber)
SchoolName
Address
DistrictID (Number)

tblDistricts
DistrictID (Autonumber)
District

tblJobTitles
JobTitleID (Autonumber)
JobTitle

tblPersons
PersonID (Autonumber)
Forename
Surname

tblPersonsToJobTitles
PersonID (Number)
JobTitleID (Number)

tblPersonsToSchools
PersonID (Number)
SchoolID (Number)

The last two tables are because some teachers can take on multiple roles (i.e. classroom teacher and guidance teacher) and because some teachers can be dynamic in their location in that they travel between schools.

This structure should simulate that.

The key:

Name of the Table
A Primary Key
A Foreign Key
A Foreign Key & Part of A Composite Primary Key

 

[cuttsy]

Thanks a lot for your time.

 

[Mile-O]

Reply back if you have any problems understanding anything (like why there's no checkbox fields for job types)...

 

[cuttsy]

Thanks, I've gone through it in access and I've got it.

I tend to be able to understand things but make me develope a system from scratch and I hit a wall. Thanks again. You've saved me a lot of time and a lot of anadins.