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
|
|||
|
|||
strategy for data entry in multiple tables
A great strength of Access is the ability to base a form on a multi-table
query and use the form for data entry. However, there are some important issues. Let us assume a simple database with two master tables and a junction table. One master table (subjects) has a primary key that is a text variable. The other master table is an event table with an autonumbered EventID as primary key. Assume for this example that tblEventID has fields EventID, Site, Date, Time. Issue 1. Should there be a single bound data entry form that has all fields from all 3 tables? For this to occur, code or macro must be written to run after the SubjectID is entered in the SubjectID field from tblSubjecst, to determine if a new record should be written in the subjects table. If a new record does not need to be made, then the SubjectsID field should be cleared, but the SubjectsID value placed in the SubjectsID field from tblJunction. Issue 2. A bigger problem occurs when data are entered for tblEvent on the single bound entry form. Code or macro must be written to determine if the non-primary key fields are already present in tblEvents. If they are, then the EventID from the matching record should be copied into EventID field on the data entry form for tblEvents. If not, then a new record must be written into tblEvent and the autonumber copied into the data transfer form. The big question is: should there be separate data entry forms for the two master tables, possibly on unbound forms that pass appropriate values to the relevant data entry forms, or to an error event? The tblJunction data entry form would start out with the passed values in the foreign key fields. Is there a better strategy for dealing data entry with referential integrity and cascade updating. LAF |
#2
|
|||
|
|||
Comments in-line
"LAF" wrote in message ... A great strength of Access is the ability to base a form on a multi-table query and use the form for data entry. However, there are some important issues. Let us assume a simple database with two master tables and a junction table. One master table (subjects) has a primary key that is a text variable. The other master table is an event table with an autonumbered EventID as primary key. Assume for this example that tblEventID has fields EventID, Site, Date, Time. Issue 1. Should there be a single bound data entry form that has all fields from all 3 tables? For this to occur, code or macro must be written to run after the SubjectID is entered in the SubjectID field from tblSubjecst, to determine if a new record should be written in the subjects table. If a new record does not need to be made, then the SubjectsID field should be cleared, but the SubjectsID value placed in the SubjectsID field from tblJunction. Maybe? What is the relationship between Subject and Event? If 1 to 1, ok. But otherwise, it seems to me subject and event should be separate forms with data from the junction table in a subform (if it makes sense to show the related data in both places). You seem to be concerned about duplicate data in subject. Ok, so, do some validation before update/insert for subject records....and if there is a duplicate, cancel the update and go to the preexisting record or prompt the user to do so. Issue 2. A bigger problem occurs when data are entered for tblEvent on the single bound entry form. Code or macro must be written to determine if the non-primary key fields are already present in tblEvents. If they are, then the EventID from the matching record should be copied into EventID field on the data entry form for tblEvents. If not, then a new record must be written into tblEvent and the autonumber copied into the data transfer form. This seems very similar to issue 1, and the solution is as well. The big question is: should there be separate data entry forms for the two master tables, possibly on unbound forms that pass appropriate values to the relevant data entry forms, or to an error event? The tblJunction data entry form would start out with the passed values in the foreign key fields. Is there a better strategy for dealing data entry with referential integrity and cascade updating. Yes 2 forms....with a subform for the junction data. Whether bound or unbound, you're going to need to do validation to ensure that any new record is not a duplicate of an existing record. I'm not sure why you'd have the junction data on yet another form, if it is related to the master tables....just use a subform if possible....then the foreign key for either subject or event would be passed automatically. LAF |
#3
|
|||
|
|||
Hi Paul,
Thanks for the advice. Let us assume that there is one to many relationship between a master table and the junction table, for each master table. There is not necessarily one to one between the two master tables. In principle then, it appears that the best strategy is to combine data entry and validation for each master table separately, and then to copy the primary field to the data entry form for the junction table. I assume that there would be no problem with referential integrity to have a new record in a master field with no foreign key, yet, put in a new record in the junction table. Is this correct? Thanks, LAF "Paul Overway" wrote: Comments in-line "LAF" wrote in message ... A great strength of Access is the ability to base a form on a multi-table query and use the form for data entry. However, there are some important issues. Let us assume a simple database with two master tables and a junction table. One master table (subjects) has a primary key that is a text variable. The other master table is an event table with an autonumbered EventID as primary key. Assume for this example that tblEventID has fields EventID, Site, Date, Time. Issue 1. Should there be a single bound data entry form that has all fields from all 3 tables? For this to occur, code or macro must be written to run after the SubjectID is entered in the SubjectID field from tblSubjecst, to determine if a new record should be written in the subjects table. If a new record does not need to be made, then the SubjectsID field should be cleared, but the SubjectsID value placed in the SubjectsID field from tblJunction. Maybe? What is the relationship between Subject and Event? If 1 to 1, ok. But otherwise, it seems to me subject and event should be separate forms with data from the junction table in a subform (if it makes sense to show the related data in both places). You seem to be concerned about duplicate data in subject. Ok, so, do some validation before update/insert for subject records....and if there is a duplicate, cancel the update and go to the preexisting record or prompt the user to do so. Issue 2. A bigger problem occurs when data are entered for tblEvent on the single bound entry form. Code or macro must be written to determine if the non-primary key fields are already present in tblEvents. If they are, then the EventID from the matching record should be copied into EventID field on the data entry form for tblEvents. If not, then a new record must be written into tblEvent and the autonumber copied into the data transfer form. This seems very similar to issue 1, and the solution is as well. The big question is: should there be separate data entry forms for the two master tables, possibly on unbound forms that pass appropriate values to the relevant data entry forms, or to an error event? The tblJunction data entry form would start out with the passed values in the foreign key fields. Is there a better strategy for dealing data entry with referential integrity and cascade updating. Yes 2 forms....with a subform for the junction data. Whether bound or unbound, you're going to need to do validation to ensure that any new record is not a duplicate of an existing record. I'm not sure why you'd have the junction data on yet another form, if it is related to the master tables....just use a subform if possible....then the foreign key for either subject or event would be passed automatically. LAF |
#4
|
|||
|
|||
My experience (admittedly limited) with junction tables has been that one of
the main tables is relatively static (such as a Student table) and the other is more dynamic (such as a Courses table). Each student can be in many courses, and each course can have many students, so a junction table resolves the many-to-many relationship. A form could be based on the Courses table (or a query based on the table), with a subform based on the junction table to list the students taking that particular course. The subform could have a combo box that has as its row source a query based on the Student table. The bound column would be the one containing the primary key. A Not In List event could open a form to add a record to the Student table, if need be. I don't know if this approach would work in your case. My point is just that the tables can all work together without being combined into a single query, and that combining them is not necessarily an advantage. You wrote "Let us assume that there is one to many relationship between a master table and the junction table, for each master table." That is generally the case with a junction table. It's what a junction table does. I don't really follow the purpose of your database, but it each subject can be associated with many events, and each event with many subjects, then you have many-to-many, and need a junction table. The junction table needs to contain fields that correspond to the primary keys from the main tables. These foreign keys are established when you set up the relationships between the tables. You don't typically enter data directly into the foreign key fields. In the example I gave, each record in the subform's record source contains as its foreign key the primary key field from the parent form's record source. I agree with what Paul said, and would tend to agree that a subform is often the most workable choice. Perhaps if you have more questions you could be more specific about what the database does, or at least use an analogous situation as an example. If you were talking about Customers and Orders, or Students and Courses the relationship would be pretty clear, but the connection between Subject and Event is not immediately apparent, at least not this early in the morning. "LAF" wrote: Hi Paul, Thanks for the advice. Let us assume that there is one to many relationship between a master table and the junction table, for each master table. There is not necessarily one to one between the two master tables. In principle then, it appears that the best strategy is to combine data entry and validation for each master table separately, and then to copy the primary field to the data entry form for the junction table. I assume that there would be no problem with referential integrity to have a new record in a master field with no foreign key, yet, put in a new record in the junction table. Is this correct? Thanks, LAF "Paul Overway" wrote: Comments in-line "LAF" wrote in message ... A great strength of Access is the ability to base a form on a multi-table query and use the form for data entry. However, there are some important issues. Let us assume a simple database with two master tables and a junction table. One master table (subjects) has a primary key that is a text variable. The other master table is an event table with an autonumbered EventID as primary key. Assume for this example that tblEventID has fields EventID, Site, Date, Time. Issue 1. Should there be a single bound data entry form that has all fields from all 3 tables? For this to occur, code or macro must be written to run after the SubjectID is entered in the SubjectID field from tblSubjecst, to determine if a new record should be written in the subjects table. If a new record does not need to be made, then the SubjectsID field should be cleared, but the SubjectsID value placed in the SubjectsID field from tblJunction. Maybe? What is the relationship between Subject and Event? If 1 to 1, ok. But otherwise, it seems to me subject and event should be separate forms with data from the junction table in a subform (if it makes sense to show the related data in both places). You seem to be concerned about duplicate data in subject. Ok, so, do some validation before update/insert for subject records....and if there is a duplicate, cancel the update and go to the preexisting record or prompt the user to do so. Issue 2. A bigger problem occurs when data are entered for tblEvent on the single bound entry form. Code or macro must be written to determine if the non-primary key fields are already present in tblEvents. If they are, then the EventID from the matching record should be copied into EventID field on the data entry form for tblEvents. If not, then a new record must be written into tblEvent and the autonumber copied into the data transfer form. This seems very similar to issue 1, and the solution is as well. The big question is: should there be separate data entry forms for the two master tables, possibly on unbound forms that pass appropriate values to the relevant data entry forms, or to an error event? The tblJunction data entry form would start out with the passed values in the foreign key fields. Is there a better strategy for dealing data entry with referential integrity and cascade updating. Yes 2 forms....with a subform for the junction data. Whether bound or unbound, you're going to need to do validation to ensure that any new record is not a duplicate of an existing record. I'm not sure why you'd have the junction data on yet another form, if it is related to the master tables....just use a subform if possible....then the foreign key for either subject or event would be passed automatically. LAF |
#5
|
|||
|
|||
I think you're misusing or misinstanding keys. You should have a PK in
Event, another PK in Subject, and 2 FK (EventID and SubjectID) in the junction. You shouldn't be putting an FK in either of the master tables. Example Event 1 Gala 2 Dinner Subject 1 Funding 2 Purpose Junction Event Subject 1 1 1 2 2 2 In the example above, I've omitted one of the possible combinations. Nevertheless, we can see for the Gala the subject will be Funding and Purpose, whereas for the Dinner the only subject will be purpose. If you have a subform for Junction on the Event form and on the Subject form, you can see what subjects will be discussed at the event or at what event the subject will be discussed. -- Paul Overway Logico Solutions http://www.logico-solutions.com "LAF" wrote in message ... Hi Paul, Thanks for the advice. Let us assume that there is one to many relationship between a master table and the junction table, for each master table. There is not necessarily one to one between the two master tables. In principle then, it appears that the best strategy is to combine data entry and validation for each master table separately, and then to copy the primary field to the data entry form for the junction table. I assume that there would be no problem with referential integrity to have a new record in a master field with no foreign key, yet, put in a new record in the junction table. Is this correct? Thanks, LAF "Paul Overway" wrote: Comments in-line "LAF" wrote in message ... A great strength of Access is the ability to base a form on a multi-table query and use the form for data entry. However, there are some important issues. Let us assume a simple database with two master tables and a junction table. One master table (subjects) has a primary key that is a text variable. The other master table is an event table with an autonumbered EventID as primary key. Assume for this example that tblEventID has fields EventID, Site, Date, Time. Issue 1. Should there be a single bound data entry form that has all fields from all 3 tables? For this to occur, code or macro must be written to run after the SubjectID is entered in the SubjectID field from tblSubjecst, to determine if a new record should be written in the subjects table. If a new record does not need to be made, then the SubjectsID field should be cleared, but the SubjectsID value placed in the SubjectsID field from tblJunction. Maybe? What is the relationship between Subject and Event? If 1 to 1, ok. But otherwise, it seems to me subject and event should be separate forms with data from the junction table in a subform (if it makes sense to show the related data in both places). You seem to be concerned about duplicate data in subject. Ok, so, do some validation before update/insert for subject records....and if there is a duplicate, cancel the update and go to the preexisting record or prompt the user to do so. Issue 2. A bigger problem occurs when data are entered for tblEvent on the single bound entry form. Code or macro must be written to determine if the non-primary key fields are already present in tblEvents. If they are, then the EventID from the matching record should be copied into EventID field on the data entry form for tblEvents. If not, then a new record must be written into tblEvent and the autonumber copied into the data transfer form. This seems very similar to issue 1, and the solution is as well. The big question is: should there be separate data entry forms for the two master tables, possibly on unbound forms that pass appropriate values to the relevant data entry forms, or to an error event? The tblJunction data entry form would start out with the passed values in the foreign key fields. Is there a better strategy for dealing data entry with referential integrity and cascade updating. Yes 2 forms....with a subform for the junction data. Whether bound or unbound, you're going to need to do validation to ensure that any new record is not a duplicate of an existing record. I'm not sure why you'd have the junction data on yet another form, if it is related to the master tables....just use a subform if possible....then the foreign key for either subject or event would be passed automatically. LAF |
#6
|
|||
|
|||
Hello All,
Thanks for the stimulating discussion. Let me go back to the original problem. The data entry form is not to look at records, it is for entering (and validating) data. I don't see how a subform for the junction table can be used appropriately with a form for just one of the master tables. The foreign key for the other master table will be present, but the data entry needs to go into the primary key of the other master table. By the way, the database I am developing is for capturing birds in mist nets. We put numbered aluminum bands on the legs of birds we capture. tblBirds lists each individual by band number (=BirdID) as the primary key, with other fields such as species, leftleg, rightleg (for those birds on which we place color bands as well). tblEvents lists each capture event by site, date, and time. The EventID is an autonumbered field that is based on unique combinations of site, date, and time. The junction table deals with the many to many relationship between BirdID and EventID. The junction table has many fields that deal with data collected on birds captured in nets, and is in one to one relationship with tables with specialized data on particular birds. This is where I see a subform for data entry being useful. If both master tables could be used in the same data entry form, then subforms would be feasible for the junction table. Is it possible for two tables to be in the same form, each with their own primary keys, and then to use the junction table as a subform? Thanks, LAF "Paul Overway" wrote: I think you're misusing or misinstanding keys. You should have a PK in Event, another PK in Subject, and 2 FK (EventID and SubjectID) in the junction. You shouldn't be putting an FK in either of the master tables. Example Event 1 Gala 2 Dinner Subject 1 Funding 2 Purpose Junction Event Subject 1 1 1 2 2 2 In the example above, I've omitted one of the possible combinations. Nevertheless, we can see for the Gala the subject will be Funding and Purpose, whereas for the Dinner the only subject will be purpose. If you have a subform for Junction on the Event form and on the Subject form, you can see what subjects will be discussed at the event or at what event the subject will be discussed. -- Paul Overway Logico Solutions http://www.logico-solutions.com "LAF" wrote in message ... Hi Paul, Thanks for the advice. Let us assume that there is one to many relationship between a master table and the junction table, for each master table. There is not necessarily one to one between the two master tables. In principle then, it appears that the best strategy is to combine data entry and validation for each master table separately, and then to copy the primary field to the data entry form for the junction table. I assume that there would be no problem with referential integrity to have a new record in a master field with no foreign key, yet, put in a new record in the junction table. Is this correct? Thanks, LAF "Paul Overway" wrote: Comments in-line "LAF" wrote in message ... A great strength of Access is the ability to base a form on a multi-table query and use the form for data entry. However, there are some important issues. Let us assume a simple database with two master tables and a junction table. One master table (subjects) has a primary key that is a text variable. The other master table is an event table with an autonumbered EventID as primary key. Assume for this example that tblEventID has fields EventID, Site, Date, Time. Issue 1. Should there be a single bound data entry form that has all fields from all 3 tables? For this to occur, code or macro must be written to run after the SubjectID is entered in the SubjectID field from tblSubjecst, to determine if a new record should be written in the subjects table. If a new record does not need to be made, then the SubjectsID field should be cleared, but the SubjectsID value placed in the SubjectsID field from tblJunction. Maybe? What is the relationship between Subject and Event? If 1 to 1, ok. But otherwise, it seems to me subject and event should be separate forms with data from the junction table in a subform (if it makes sense to show the related data in both places). You seem to be concerned about duplicate data in subject. Ok, so, do some validation before update/insert for subject records....and if there is a duplicate, cancel the update and go to the preexisting record or prompt the user to do so. Issue 2. A bigger problem occurs when data are entered for tblEvent on the single bound entry form. Code or macro must be written to determine if the non-primary key fields are already present in tblEvents. If they are, then the EventID from the matching record should be copied into EventID field on the data entry form for tblEvents. If not, then a new record must be written into tblEvent and the autonumber copied into the data transfer form. This seems very similar to issue 1, and the solution is as well. The big question is: should there be separate data entry forms for the two master tables, possibly on unbound forms that pass appropriate values to the relevant data entry forms, or to an error event? The tblJunction data entry form would start out with the passed values in the foreign key fields. Is there a better strategy for dealing data entry with referential integrity and cascade updating. Yes 2 forms....with a subform for the junction data. Whether bound or unbound, you're going to need to do validation to ensure that any new record is not a duplicate of an existing record. I'm not sure why you'd have the junction data on yet another form, if it is related to the master tables....just use a subform if possible....then the foreign key for either subject or event would be passed automatically. LAF |
#7
|
|||
|
|||
Ah...ok. I see what you're trying to do now.
I'd have a single unbound entry form for this, with 2 listboxs...one for Birds, and another for Events. I'd have an Add button that would add a record to a subform for the Junction data. I'd use the selections from the 2 listboxes in the link master/link child property for the subform, which would allow you to see how the 2 master tables are related and the junction data for that combination. -- Paul Overway Logico Solutions http://www.logico-solutions.com "LAF" wrote in message ... Hello All, Thanks for the stimulating discussion. Let me go back to the original problem. The data entry form is not to look at records, it is for entering (and validating) data. I don't see how a subform for the junction table can be used appropriately with a form for just one of the master tables. The foreign key for the other master table will be present, but the data entry needs to go into the primary key of the other master table. By the way, the database I am developing is for capturing birds in mist nets. We put numbered aluminum bands on the legs of birds we capture. tblBirds lists each individual by band number (=BirdID) as the primary key, with other fields such as species, leftleg, rightleg (for those birds on which we place color bands as well). tblEvents lists each capture event by site, date, and time. The EventID is an autonumbered field that is based on unique combinations of site, date, and time. The junction table deals with the many to many relationship between BirdID and EventID. The junction table has many fields that deal with data collected on birds captured in nets, and is in one to one relationship with tables with specialized data on particular birds. This is where I see a subform for data entry being useful. If both master tables could be used in the same data entry form, then subforms would be feasible for the junction table. Is it possible for two tables to be in the same form, each with their own primary keys, and then to use the junction table as a subform? Thanks, LAF "Paul Overway" wrote: I think you're misusing or misinstanding keys. You should have a PK in Event, another PK in Subject, and 2 FK (EventID and SubjectID) in the junction. You shouldn't be putting an FK in either of the master tables. Example Event 1 Gala 2 Dinner Subject 1 Funding 2 Purpose Junction Event Subject 1 1 1 2 2 2 In the example above, I've omitted one of the possible combinations. Nevertheless, we can see for the Gala the subject will be Funding and Purpose, whereas for the Dinner the only subject will be purpose. If you have a subform for Junction on the Event form and on the Subject form, you can see what subjects will be discussed at the event or at what event the subject will be discussed. -- Paul Overway Logico Solutions http://www.logico-solutions.com "LAF" wrote in message ... Hi Paul, Thanks for the advice. Let us assume that there is one to many relationship between a master table and the junction table, for each master table. There is not necessarily one to one between the two master tables. In principle then, it appears that the best strategy is to combine data entry and validation for each master table separately, and then to copy the primary field to the data entry form for the junction table. I assume that there would be no problem with referential integrity to have a new record in a master field with no foreign key, yet, put in a new record in the junction table. Is this correct? Thanks, LAF "Paul Overway" wrote: Comments in-line "LAF" wrote in message ... A great strength of Access is the ability to base a form on a multi-table query and use the form for data entry. However, there are some important issues. Let us assume a simple database with two master tables and a junction table. One master table (subjects) has a primary key that is a text variable. The other master table is an event table with an autonumbered EventID as primary key. Assume for this example that tblEventID has fields EventID, Site, Date, Time. Issue 1. Should there be a single bound data entry form that has all fields from all 3 tables? For this to occur, code or macro must be written to run after the SubjectID is entered in the SubjectID field from tblSubjecst, to determine if a new record should be written in the subjects table. If a new record does not need to be made, then the SubjectsID field should be cleared, but the SubjectsID value placed in the SubjectsID field from tblJunction. Maybe? What is the relationship between Subject and Event? If 1 to 1, ok. But otherwise, it seems to me subject and event should be separate forms with data from the junction table in a subform (if it makes sense to show the related data in both places). You seem to be concerned about duplicate data in subject. Ok, so, do some validation before update/insert for subject records....and if there is a duplicate, cancel the update and go to the preexisting record or prompt the user to do so. Issue 2. A bigger problem occurs when data are entered for tblEvent on the single bound entry form. Code or macro must be written to determine if the non-primary key fields are already present in tblEvents. If they are, then the EventID from the matching record should be copied into EventID field on the data entry form for tblEvents. If not, then a new record must be written into tblEvent and the autonumber copied into the data transfer form. This seems very similar to issue 1, and the solution is as well. The big question is: should there be separate data entry forms for the two master tables, possibly on unbound forms that pass appropriate values to the relevant data entry forms, or to an error event? The tblJunction data entry form would start out with the passed values in the foreign key fields. Is there a better strategy for dealing data entry with referential integrity and cascade updating. Yes 2 forms....with a subform for the junction data. Whether bound or unbound, you're going to need to do validation to ensure that any new record is not a duplicate of an existing record. I'm not sure why you'd have the junction data on yet another form, if it is related to the master tables....just use a subform if possible....then the foreign key for either subject or event would be passed automatically. LAF |
#8
|
|||
|
|||
Interesting discussion, but it leaves me wondering how tblEvents and tblBirds
are related many-to-many unless an individual bird could be captured several times. I see how each event could involve many birds, but not the other way around. What relationship is the junction table resolving? I would have imagined based on my understanding of the question that it would be Events Birds Bird Details in a cascading one-to-many relationship, with nested subforms. I don't really need an answer, but I am a bit curious as to how this discussion will unfold. "Paul Overway" wrote: Ah...ok. I see what you're trying to do now. I'd have a single unbound entry form for this, with 2 listboxs...one for Birds, and another for Events. I'd have an Add button that would add a record to a subform for the Junction data. I'd use the selections from the 2 listboxes in the link master/link child property for the subform, which would allow you to see how the 2 master tables are related and the junction data for that combination. -- Paul Overway Logico Solutions http://www.logico-solutions.com "LAF" wrote in message ... Hello All, Thanks for the stimulating discussion. Let me go back to the original problem. The data entry form is not to look at records, it is for entering (and validating) data. I don't see how a subform for the junction table can be used appropriately with a form for just one of the master tables. The foreign key for the other master table will be present, but the data entry needs to go into the primary key of the other master table. By the way, the database I am developing is for capturing birds in mist nets. We put numbered aluminum bands on the legs of birds we capture. tblBirds lists each individual by band number (=BirdID) as the primary key, with other fields such as species, leftleg, rightleg (for those birds on which we place color bands as well). tblEvents lists each capture event by site, date, and time. The EventID is an autonumbered field that is based on unique combinations of site, date, and time. The junction table deals with the many to many relationship between BirdID and EventID. The junction table has many fields that deal with data collected on birds captured in nets, and is in one to one relationship with tables with specialized data on particular birds. This is where I see a subform for data entry being useful. If both master tables could be used in the same data entry form, then subforms would be feasible for the junction table. Is it possible for two tables to be in the same form, each with their own primary keys, and then to use the junction table as a subform? Thanks, LAF "Paul Overway" wrote: I think you're misusing or misinstanding keys. You should have a PK in Event, another PK in Subject, and 2 FK (EventID and SubjectID) in the junction. You shouldn't be putting an FK in either of the master tables. Example Event 1 Gala 2 Dinner Subject 1 Funding 2 Purpose Junction Event Subject 1 1 1 2 2 2 In the example above, I've omitted one of the possible combinations. Nevertheless, we can see for the Gala the subject will be Funding and Purpose, whereas for the Dinner the only subject will be purpose. If you have a subform for Junction on the Event form and on the Subject form, you can see what subjects will be discussed at the event or at what event the subject will be discussed. -- Paul Overway Logico Solutions http://www.logico-solutions.com "LAF" wrote in message ... Hi Paul, Thanks for the advice. Let us assume that there is one to many relationship between a master table and the junction table, for each master table. There is not necessarily one to one between the two master tables. In principle then, it appears that the best strategy is to combine data entry and validation for each master table separately, and then to copy the primary field to the data entry form for the junction table. I assume that there would be no problem with referential integrity to have a new record in a master field with no foreign key, yet, put in a new record in the junction table. Is this correct? Thanks, LAF "Paul Overway" wrote: Comments in-line "LAF" wrote in message ... A great strength of Access is the ability to base a form on a multi-table query and use the form for data entry. However, there are some important issues. Let us assume a simple database with two master tables and a junction table. One master table (subjects) has a primary key that is a text variable. The other master table is an event table with an autonumbered EventID as primary key. Assume for this example that tblEventID has fields EventID, Site, Date, Time. Issue 1. Should there be a single bound data entry form that has all fields from all 3 tables? For this to occur, code or macro must be written to run after the SubjectID is entered in the SubjectID field from tblSubjecst, to determine if a new record should be written in the subjects table. If a new record does not need to be made, then the SubjectsID field should be cleared, but the SubjectsID value placed in the SubjectsID field from tblJunction. Maybe? What is the relationship between Subject and Event? If 1 to 1, ok. But otherwise, it seems to me subject and event should be separate forms with data from the junction table in a subform (if it makes sense to show the related data in both places). You seem to be concerned about duplicate data in subject. Ok, so, do some validation before update/insert for subject records....and if there is a duplicate, cancel the update and go to the preexisting record or prompt the user to do so. Issue 2. A bigger problem occurs when data are entered for tblEvent on the single bound entry form. Code or macro must be written to determine if the non-primary key fields are already present in tblEvents. If they are, then the EventID from the matching record should be copied into EventID field on the data entry form for tblEvents. If not, then a new record must be written into tblEvent and the autonumber copied into the data transfer form. This seems very similar to issue 1, and the solution is as well. The big question is: should there be separate data entry forms for the two master tables, possibly on unbound forms that pass appropriate values to the relevant data entry forms, or to an error event? The tblJunction data entry form would start out with the passed values in the foreign key fields. Is there a better strategy for dealing data entry with referential integrity and cascade updating. Yes 2 forms....with a subform for the junction data. Whether bound or unbound, you're going to need to do validation to ensure that any new record is not a duplicate of an existing record. I'm not sure why you'd have the junction data on yet another form, if it is related to the master tables....just use a subform if possible....then the foreign key for either subject or event would be passed automatically. LAF |
#9
|
|||
|
|||
It makes sense because birds are being captured collectively as a group in
nets. If they were being captured individually, the junction would be unnecessary....a bird would be related to events in a one to many. But because the birds are captured collectively, the junction is used to resolve what birds were captured during an event and vice versa. -- Paul Overway Logico Solutions http://www.logico-solutions.com "BruceM" wrote in message ... Interesting discussion, but it leaves me wondering how tblEvents and tblBirds are related many-to-many unless an individual bird could be captured several times. I see how each event could involve many birds, but not the other way around. What relationship is the junction table resolving? I would have imagined based on my understanding of the question that it would be Events Birds Bird Details in a cascading one-to-many relationship, with nested subforms. I don't really need an answer, but I am a bit curious as to how this discussion will unfold. "Paul Overway" wrote: Ah...ok. I see what you're trying to do now. I'd have a single unbound entry form for this, with 2 listboxs...one for Birds, and another for Events. I'd have an Add button that would add a record to a subform for the Junction data. I'd use the selections from the 2 listboxes in the link master/link child property for the subform, which would allow you to see how the 2 master tables are related and the junction data for that combination. -- Paul Overway Logico Solutions http://www.logico-solutions.com "LAF" wrote in message ... Hello All, Thanks for the stimulating discussion. Let me go back to the original problem. The data entry form is not to look at records, it is for entering (and validating) data. I don't see how a subform for the junction table can be used appropriately with a form for just one of the master tables. The foreign key for the other master table will be present, but the data entry needs to go into the primary key of the other master table. By the way, the database I am developing is for capturing birds in mist nets. We put numbered aluminum bands on the legs of birds we capture. tblBirds lists each individual by band number (=BirdID) as the primary key, with other fields such as species, leftleg, rightleg (for those birds on which we place color bands as well). tblEvents lists each capture event by site, date, and time. The EventID is an autonumbered field that is based on unique combinations of site, date, and time. The junction table deals with the many to many relationship between BirdID and EventID. The junction table has many fields that deal with data collected on birds captured in nets, and is in one to one relationship with tables with specialized data on particular birds. This is where I see a subform for data entry being useful. If both master tables could be used in the same data entry form, then subforms would be feasible for the junction table. Is it possible for two tables to be in the same form, each with their own primary keys, and then to use the junction table as a subform? Thanks, LAF "Paul Overway" wrote: I think you're misusing or misinstanding keys. You should have a PK in Event, another PK in Subject, and 2 FK (EventID and SubjectID) in the junction. You shouldn't be putting an FK in either of the master tables. Example Event 1 Gala 2 Dinner Subject 1 Funding 2 Purpose Junction Event Subject 1 1 1 2 2 2 In the example above, I've omitted one of the possible combinations. Nevertheless, we can see for the Gala the subject will be Funding and Purpose, whereas for the Dinner the only subject will be purpose. If you have a subform for Junction on the Event form and on the Subject form, you can see what subjects will be discussed at the event or at what event the subject will be discussed. -- Paul Overway Logico Solutions http://www.logico-solutions.com "LAF" wrote in message ... Hi Paul, Thanks for the advice. Let us assume that there is one to many relationship between a master table and the junction table, for each master table. There is not necessarily one to one between the two master tables. In principle then, it appears that the best strategy is to combine data entry and validation for each master table separately, and then to copy the primary field to the data entry form for the junction table. I assume that there would be no problem with referential integrity to have a new record in a master field with no foreign key, yet, put in a new record in the junction table. Is this correct? Thanks, LAF "Paul Overway" wrote: Comments in-line "LAF" wrote in message ... A great strength of Access is the ability to base a form on a multi-table query and use the form for data entry. However, there are some important issues. Let us assume a simple database with two master tables and a junction table. One master table (subjects) has a primary key that is a text variable. The other master table is an event table with an autonumbered EventID as primary key. Assume for this example that tblEventID has fields EventID, Site, Date, Time. Issue 1. Should there be a single bound data entry form that has all fields from all 3 tables? For this to occur, code or macro must be written to run after the SubjectID is entered in the SubjectID field from tblSubjecst, to determine if a new record should be written in the subjects table. If a new record does not need to be made, then the SubjectsID field should be cleared, but the SubjectsID value placed in the SubjectsID field from tblJunction. Maybe? What is the relationship between Subject and Event? If 1 to 1, ok. But otherwise, it seems to me subject and event should be separate forms with data from the junction table in a subform (if it makes sense to show the related data in both places). You seem to be concerned about duplicate data in subject. Ok, so, do some validation before update/insert for subject records....and if there is a duplicate, cancel the update and go to the preexisting record or prompt the user to do so. Issue 2. A bigger problem occurs when data are entered for tblEvent on the single bound entry form. Code or macro must be written to determine if the non-primary key fields are already present in tblEvents. If they are, then the EventID from the matching record should be copied into EventID field on the data entry form for tblEvents. If not, then a new record must be written into tblEvent and the autonumber copied into the data transfer form. This seems very similar to issue 1, and the solution is as well. The big question is: should there be separate data entry forms for the two master tables, possibly on unbound forms that pass appropriate values to the relevant data entry forms, or to an error event? The tblJunction data entry form would start out with the passed values in the foreign key fields. Is there a better strategy for dealing data entry with referential integrity and cascade updating. Yes 2 forms....with a subform for the junction data. Whether bound or unbound, you're going to need to do validation to ensure that any new record is not a duplicate of an existing record. I'm not sure why you'd have the junction data on yet another form, if it is related to the master tables....just use a subform if possible....then the foreign key for either subject or event would be passed automatically. LAF |
#10
|
|||
|
|||
Thanks Paul and Bruce,
This is what makes the community group so valuable. The issue of data entry for master tables and junction tables, with referential integrity and cascading update, has to be one of the most general in Access. It is completely neglected in the 5 or so books I have on Access. The following subissues are involved in every single access data base with referential integrity. 1. Need to determine if a new record needs to be written in neither, one, or both master tables. 2. The master table primary key values, whether for newly written record or previous record, need to be entered into appropriate fields of the junction table data entry form. I am currently experimenting with a 3-form model, one form for each master table and one for the junction table. All 3 will be on screen. The user will first use one of the master table forms to determine if a new record needs to be written. If so, the data entered on this form will be used to create the new record, and the primary key value will be copied to the foreign key field on the junction data entry form. If a new record does not need to be written, the primary key value that was inputted will copied to the foreign key field (through code). This procedure will occur for each master table in sequence. The user will simply be left with the larger junction table data entry form with the foreign keys filled in from the previous master table forms. Subforms can then be used for data tables that are in one to one correspondence with the junction table. This is a little involved, but the code that underlies this serves for data validation as well as entry. My question thus becomes: is there a simpler way to get the foreign key values on the junction table data entry form? All the best, LAF "Paul Overway" wrote: It makes sense because birds are being captured collectively as a group in nets. If they were being captured individually, the junction would be unnecessary....a bird would be related to events in a one to many. But because the birds are captured collectively, the junction is used to resolve what birds were captured during an event and vice versa. -- Paul Overway Logico Solutions http://www.logico-solutions.com "BruceM" wrote in message ... Interesting discussion, but it leaves me wondering how tblEvents and tblBirds are related many-to-many unless an individual bird could be captured several times. I see how each event could involve many birds, but not the other way around. What relationship is the junction table resolving? I would have imagined based on my understanding of the question that it would be Events Birds Bird Details in a cascading one-to-many relationship, with nested subforms. I don't really need an answer, but I am a bit curious as to how this discussion will unfold. "Paul Overway" wrote: Ah...ok. I see what you're trying to do now. I'd have a single unbound entry form for this, with 2 listboxs...one for Birds, and another for Events. I'd have an Add button that would add a record to a subform for the Junction data. I'd use the selections from the 2 listboxes in the link master/link child property for the subform, which would allow you to see how the 2 master tables are related and the junction data for that combination. -- Paul Overway Logico Solutions http://www.logico-solutions.com "LAF" wrote in message ... Hello All, Thanks for the stimulating discussion. Let me go back to the original problem. The data entry form is not to look at records, it is for entering (and validating) data. I don't see how a subform for the junction table can be used appropriately with a form for just one of the master tables. The foreign key for the other master table will be present, but the data entry needs to go into the primary key of the other master table. By the way, the database I am developing is for capturing birds in mist nets. We put numbered aluminum bands on the legs of birds we capture. tblBirds lists each individual by band number (=BirdID) as the primary key, with other fields such as species, leftleg, rightleg (for those birds on which we place color bands as well). tblEvents lists each capture event by site, date, and time. The EventID is an autonumbered field that is based on unique combinations of site, date, and time. The junction table deals with the many to many relationship between BirdID and EventID. The junction table has many fields that deal with data collected on birds captured in nets, and is in one to one relationship with tables with specialized data on particular birds. This is where I see a subform for data entry being useful. If both master tables could be used in the same data entry form, then subforms would be feasible for the junction table. Is it possible for two tables to be in the same form, each with their own primary keys, and then to use the junction table as a subform? Thanks, LAF "Paul Overway" wrote: I think you're misusing or misinstanding keys. You should have a PK in Event, another PK in Subject, and 2 FK (EventID and SubjectID) in the junction. You shouldn't be putting an FK in either of the master tables. Example Event 1 Gala 2 Dinner Subject 1 Funding 2 Purpose Junction Event Subject 1 1 1 2 2 2 In the example above, I've omitted one of the possible combinations. Nevertheless, we can see for the Gala the subject will be Funding and Purpose, whereas for the Dinner the only subject will be purpose. If you have a subform for Junction on the Event form and on the Subject form, you can see what subjects will be discussed at the event or at what event the subject will be discussed. -- Paul Overway Logico Solutions http://www.logico-solutions.com "LAF" wrote in message ... Hi Paul, Thanks for the advice. Let us assume that there is one to many relationship between a master table and the junction table, for each master table. There is not necessarily one to one between the two master tables. In principle then, it appears that the best strategy is to combine data entry and validation for each master table separately, and then to copy the primary field to the data entry form for the junction table. I assume that there would be no problem with referential integrity to have a new record in a master field with no foreign key, yet, put in a new record in the junction table. Is this correct? Thanks, LAF "Paul Overway" wrote: Comments in-line "LAF" wrote in message ... A great strength of Access is the ability to base a form on a multi-table query and use the form for data entry. However, there are some important issues. Let us assume a simple database with two master tables and a junction table. One master table (subjects) has a primary key that is a text variable. The other master table is an event table with an autonumbered EventID as primary key. Assume for this example that tblEventID has fields EventID, Site, Date, Time. Issue 1. Should there be a single bound data entry form that has all fields from all 3 tables? For this to occur, code or macro must be written to run after the SubjectID is entered in the SubjectID field from tblSubjecst, to determine if a new record should be written in the subjects table. If a new record does not need to be made, then the SubjectsID field should be cleared, but the SubjectsID value placed in the SubjectsID field from tblJunction. Maybe? What is the relationship between Subject and Event? If 1 to 1, ok. But otherwise, it seems to me subject and event should be separate forms with data from the junction table in a subform (if it makes sense to show the related data in both places). You seem to be concerned about duplicate data in subject. Ok, so, do some validation before update/insert for subject records....and if there is a duplicate, cancel the update and go to the preexisting record or prompt the user to do so. Issue 2. A bigger problem occurs when data are entered for tblEvent on the single bound entry form. Code or macro must be written to determine if the non-primary key fields are already present in tblEvents. If they are, then the EventID from the matching record should be copied into EventID field on the data entry form for tblEvents. If not, then a new record must be written into tblEvent and the autonumber copied into the data transfer form. This seems very similar to issue 1, and the solution is as well. The big question is: should there be separate data entry forms for the two master tables, possibly on unbound forms that pass appropriate values to the relevant data entry forms, or to an error event? The tblJunction data entry form would start out with the passed values in the foreign key fields. Is there a better strategy for dealing data entry with referential integrity and cascade updating. Yes 2 forms....with a subform for the junction data. Whether bound or unbound, you're going to need to do validation to ensure that any new record is not a duplicate of an existing record. I'm not sure why you'd have the junction data on yet another form, if it is related to the master tables....just use a subform if possible....then the foreign key for either subject or event would be passed automatically. LAF |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Another Multiple Entry | TJ | General Discussion | 2 | February 18th, 2005 04:35 PM |
Criterion - How to Write Query for Multiple Tables | jcinn | Running & Setting Up Queries | 1 | February 8th, 2005 12:42 PM |
Multiple tables - Multiple forms (Access 2000) | Alastair | Using Forms | 0 | November 12th, 2004 03:30 PM |
Querying from multiple tables - newbie question | Simon Pleasants | General Discussion | 2 | August 26th, 2004 03:02 PM |