confuzzled noob

[Guest]

hi,

i need help. i'm quite experienced with excel and general applications, but
haven't got much experience with access.

i need to create a db to hold modules (or courses), occurences (when the
modules happen), and tutors.

there are many modules, 001, 002, 003 etc..

each module can have several occurences in a year, for example, one starting
september every week on a wednesday is code a, one starting in december
fortnightly is code b etc..
(so then we have 001a, 001b...002a, 002b...)

and each occurence can have different tutors eg. prof smith can teach
occurence a, but prof smith AND prof jones will teach occurence b, while prof
jones will teach occurence c.

i want to be able to see, for each module's occurence, who the tutors are,
and display them all if there are multiple tutors.

i also want this to be in a form so that data can be entered this way.

can i do this? HOW? which are primary keys?

many many many thanks,

suzie

)

 

[Joseph Meehan]

hi,

i need help. i'm quite experienced with excel and general
applications, but haven't got much experience with access.

i need to create a db to hold modules (or courses), occurences (when
the modules happen), and tutors.

there are many modules, 001, 002, 003 etc..

each module can have several occurences in a year, for example, one
starting september every week on a wednesday is code a, one starting
in december fortnightly is code b etc..
(so then we have 001a, 001b...002a, 002b...)

and each occurence can have different tutors eg. prof smith can teach
occurence a, but prof smith AND prof jones will teach occurence b,
while prof jones will teach occurence c.

i want to be able to see, for each module's occurence, who the tutors
are, and display them all if there are multiple tutors.

i also want this to be in a form so that data can be entered this way.

can i do this? HOW? which are primary keys?

many many many thanks,

suzie

It sounds like you need three tables and a primary key for each table.

 

[Guest]

i've tried that but with no success, perhaps you could advise me the best way
to do that, and how to create the form?

thanks again,

 

[Joseph Meehan]

i've tried that but with no success, perhaps you could advise me the
best way to do that, and how to create the form?

Moduletbl: listing each module and containing one autonumber primary key
field and additional files as might be needed to describe each module.

Occurencetlb: listing each occurrence and containing one autonumber
primary key field and additional files as might be needed to describe each
occurrence

Tutortbl: listing each tutor and containing one autonumber primary key
field and additional files as might be needed to describe each tutor.

Let's look at the tutor table. It would likely have a last name, first
name and middle name as well as maybe birthday, address etc. You would not
include a list of children however since they may have more than one child.
Those children would have their own table (childtbl) and it would relate to
the tutor table. Use that same idea for the other tables as you may need to
"normalize" them as well.

 

[ChrisM]

Hmmm, off the top of my head, you could design somthing like this:

ModulesTable
==========

ModuleId - Your 001, 002 etc.
Description - and whatever other fields you need to describe the course.

OccurancesTable
============
OccuranceId - AutoNumber Key to reference each occurance
OccuranceName - Name (eg a,b) and whatever other fields you need to
describe the occurance
StartDate
Frequency
ModuleId - The Module that this Occurance relates to.

TutorsTable (Details of the Tutors - Name, Address etc etc)
========
TutorId
TutorName

TutorsTeachingOccurances (Pairs to show which Tutors teach which occurances.
This allows multiple Tutors/Occurance)
==================
OccuranceId
TutorId

So Using your example below. If Tutor 1 was Smith, Tutor 2 was Jones.
OccuranceA was OccuranceId 1, OccuranceB Id 2 and C Id3

Your TutorsTeachingOccurance Table would be

Occurance Tutor
1 1
2 1
2 2
3 2

Make some sort of sense?? Or clear as mud?? ;-)

Cheers,

Chris.

 

[Larry Linson]

And, because you have indicated that one professor may tutor multiple
occurrences, and a given occurrence may be tutored by multiple professors,
you will ALSO need a junction or intersection table to represent the
many-to-many relationship. It will contain the Key of the Occurrence and the
Key of the Professor, plus any other information specifically relating to
that particular occurrence and particular professor's involvement with it.

Larry Linson
Microsoft Access MVP