If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Circuitous Relationship
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. |
#2
|
|||
|
|||
Circuitous Relationship
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" wrote in message ... 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. |
#3
|
|||
|
|||
Circuitous Relationship
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" wrote: 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" wrote in message ... 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. |
#4
|
|||
|
|||
Circuitous Relationship
you're welcome
"Will" wrote in message ... 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" wrote: 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" wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|