Help with complicated database relationships

[Clare]

I have been working on a course consultants database for a while and I can't seem to get my head round the relationships and so need an 'outside' opinion.

One table contains the course consultants information - department, faculty, email, phone number
Another table contains the course information, programe, level, course tutor, course tutor details, whether it's running...
A third table is a junction table between the two, one course can have more than one course consultant and one staff member can be a course consultant for more than one course, this table links the two and also contains yes/no fields for the years so I can record the term of appointment for each individual consultant for each individual course.
I have two forms, one to enter a new consultant, one to enter a new course, now how do I get both of those together to specify who is the consultant for which course. I will need to create two reports, one listing all the courses and the respective consultant, another grouped by consultant listing how many courses they are consultant on.

Have I got my relationship right using the junction table? Now I need a form to specify which consultant is on which course. I think I'm going round and round in circles and so any help would be greatly appreciated.

 

[Allen Browne]

Using a junction table is correct, but the check box idea is wrong.

Over time, you will have many instances of any course. A person may be the
course consultant for only part of a year (e.g. due to illness), or in some
cases you may end up with more than one course instance in a year (e.g.
course run twice, or dual streams).

The junction table probably needs fields like this:
CourseInstanceID Primary key
CourseID foreign key to the Course table.
ConsultantID foreign key to Conultant table
StartDate Date/time when this course instance begins.

The current consultant is for a course is:
SELECT TOP 1 ConsulantID FROM CourseInstance
WHERE CourseID = 999 ORDER BY StartDate DESC;

DLookup() can't really do that for you, but there is a replacement function
that can (and runs faster) here:
http://members.iinet.net.au/~allenbrowne/ser-42.html
Usage:
=ELookup("ConsultantID", "CourseInstance", "CourseID = 999", "StartDate
DESC")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have been working on a course consultants database for a while and I

can't seem to get my head round the relationships and so need an 'outside'
opinion.

One table contains the course consultants information - department, faculty, email, phone number
Another table contains the course information, programe, level, course

tutor, course tutor details, whether it's running...

A third table is a junction table between the two, one course can have

more than one course consultant and one staff member can be a course
consultant for more than one course, this table links the two and also
contains yes/no fields for the years so I can record the term of appointment
for each individual consultant for each individual course.

I have two forms, one to enter a new consultant, one to enter a new

course, now how do I get both of those together to specify who is the
consultant for which course. I will need to create two reports, one listing
all the courses and the respective consultant, another grouped by consultant
listing how many courses they are consultant on.

Have I got my relationship right using the junction table? Now I need a

form to specify which consultant is on which course. I think I'm going round
and round in circles and so any help would be greatly appreciated.