Circuitous Relationship

[Will]

I assume that I'm dealing with a circuitous relationship problem since I
can't seem to resolve what is occuring. I am rebuilding a training database
for the military. Here is the structure I have thus far:

CrewInformation (contains basic member data)
Schools (contains information about the schools/NECs)
SchoolRecord (links the crew with a school for a specific convene/grad date)

All of that works fine; except when I throw in the idea of CPD's. A CPD is
a code for a specific course offered at a specific schoolhouse. Thus a
single school (like Boarding Officer) might be offered at multiple CPD's (San
Diego, Mayport, Norfolk). I want to be able to specify on the SchoolRecord
that a member will or has gone to a certain location. The way I originally
intended was to first pick a School, then that would limit the choices of
CPD's to only those where that school is offered.

When I link those 3 tables (SchoolRecord, Schools, CPD's) it creates a loop.
Not 100% sure that's a problem but the idea would be that 1 School could
have multiple CPD's, 1 School could be on multiple SchoolRecords, and 1 CPD
could also be on multiple SchoolRecords. Problem is that whenever I work a
form or dropdown with this type of relationship I either get an empty
dropdown or a grey box. As soon as I break the circle I can get all CPD's,
but not limited by the selected school on the SchoolRecord.

While I could store CPD in SchoolRecord and remove the relationship to
Schools; sometimes I don't know which school house a member attended, just
that they have the school, so I didn't want to be constrained to selecting a
CPD if it isn't known.

 

[tina]

okay, suggest you forget forms for the moment, and concentrate on clearly
defining your tables/relationships.

the idea would be that 1 School could
have multiple CPD's, 1 School could be on multiple SchoolRecords, and 1 CPD
could also be on multiple SchoolRecords.

you've made a start here. what i gather is the following relationships, as

one school may offer many CPDs, *and* one CPD may be offered at many
schools.
one school may teach many crewmembers, *and* one crewmember may attend many
schools.
one CPD may be taught to many crewmembers, *and* one crewmember may enroll
in many CPDs.

(note: defining a relationship means you have to define "both sides", the
A-to-B side *and* the B-to-A side. a common mistake of inexperienced
developers is to forget to define the "second half" of the relationship, the
B-to-A side.)
the above are all many-to-many relationships, which are modeled in Access
using linking tables. so let's look at the following tables/relationships,
as

tblSchools
tblCourses
tblCourseSchools (linking table between schools and courses)
tblCrewmembers
tblCrewmemberCourses (linking table between crewmembers and courses)

suggest that tblCourses include not only CPDs, but also courses that are
currently taught at only one specific school. in tblCourseSchools, you'll
have one record for every school/course combination. if a single course is
taught at one school, you'll have one record; if another course is taught at
20 schools, you'll have 20 records - again, one record for each
course/school combination.

the same would apply to tblCrewmemberCourses - one record for each specific
course taken by each specific crewmember at a specific location. include a
field linking this table to tblSchools, and another field linking the table
to tblCourses. in your data entry form, base the schools combobox RowSource
on tblSchools, and base the courses combobox RowSource on tblCourseSchools -
using a query that filters the records from the selection entered in the
schools combobox.

hth

 

[Will]

That's essentially what I decided shortly after I posted. The difference is
that CPD is a unique number to a school house and course offered there. Thus
a school house that offers 3 courses would have 3 CPD's. Tracking a table
for school houses is a big more granularity than I need right now and if I
decide to change the structure to allow for it late I don't think I'll have a
hard time working that in.

So for now I removed the relationship between the actual course and the
SchoolRecord, instead linking to the CPD. Then I already have CPD's
associated with courses; so I'll just have to dropdown on the SchoolRecord
show courses and CPD's, then store the CPD for that specific record.

Thanks for confirming that I was thinking about it the right way.

 

[tina]

you're welcome

That's essentially what I decided shortly after I posted. The difference is
that CPD is a unique number to a school house and course offered there. Thus
a school house that offers 3 courses would have 3 CPD's. Tracking a table
for school houses is a big more granularity than I need right now and if I
decide to change the structure to allow for it late I don't think I'll have a
hard time working that in.

So for now I removed the relationship between the actual course and the
SchoolRecord, instead linking to the CPD. Then I already have CPD's
associated with courses; so I'll just have to dropdown on the SchoolRecord
show courses and CPD's, then store the CPD for that specific record.

Thanks for confirming that I was thinking about it the right way.