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
|
|||
|
|||
Confusion with forms and related subforms
Hello,
I'm trying to figure out the concept of embedding subforms into main forms. I have main tables, 4 junction tables, and a few look-up tables (not all look-ups are listed below). If I'm reading it correctly, the junction tables which relate to the main tables should be in the form of a subform placed within the main form they relate to? I'm lost on the fact that I believe I have a subform which appears should be on another subform which should then be on a form. If this is correct, the form looks extremely busy and not user friendly, and just plain incorrect. Is it true, based on the relationships, that the PK and FK on the related tables should be joined between the subform and main form? These are the tables I have: TblIncident IncidentnumberID (PK) misc. fields TblSuspect SuspectID (PK) misc. fields TblOfficer OfficerBadgeNumberID (PK) OfficerLastName OfficerFirstName TblIncidentOfficer IncidentOfficerID (PK) IncidentNumberID (FK) OfficerBadgeNumberID (FK) TblIncidentSuspect IncidentSuspectID (PK) IncidentNumberID (FK) SuspectID (FK) TblIncidentSuspectCharges IncidentSuspectChargesID (PK) IncidentSuspectID (FK) ChargesID (FK) TblForceUsed ForceUsedID (PK) OfficerBadgeNumber (FK) TypeofForceID (FK) TblCharges ChargesID (PK) Charges Thanks in advance for any assistance. |
#2
|
|||
|
|||
Confusion with forms and related subforms
Lee Ann -
A subform is used to help show the 'many' side of a one-to-many relationship. For many databases, this is a straight-forward relationship. It gets more complicated with the many-to-many relationships such as those in your database. While the theory still holds, you have to adjust it to your needs. For example, you might want to show suspects with all their incidents. The suspect table would be the recordsource for the main form, and Incident information would be used in the subform. This Incident information would normally be a query based on the IncidentSuspect table joined to the Incident table. You could also show Officers on a main form with Incidents on a subform this way, and many other one-to-many relationships. I have a feeling you want to show everything about an incident on one form. In that case, the Incident table would be used for the main form, and you may have several subforms - one for the officers, one for the suspects. The suspect subform would probably have a recordsource of a query based on TblIncidentSuspect joined with TblSuspect, and would be linked to the main form through the IncidentNumberID. This suspect main form may include a subform of its own to show the charges against the suspects. This sub-subform would probably have as its record source a query that joins the tblCharges to the tblIncidentSuspectCharges, and would be linked to the IncidentSuspect subform based on the IncidentSuspectID field. Similarly, the Officer subform might have a subform of it's own to show the Force Used. This data would come from a query based on the the TblForceUsed joined to the TypeOfForce table. Trying to do this will point out that you need to change your TblForceUsed table - instead of OfficerBadgeNumber, you need the IncidentOfficerID as the foreign key. Hope that helps! -- Daryl S "Lee Ann" wrote: Hello, I'm trying to figure out the concept of embedding subforms into main forms. I have main tables, 4 junction tables, and a few look-up tables (not all look-ups are listed below). If I'm reading it correctly, the junction tables which relate to the main tables should be in the form of a subform placed within the main form they relate to? I'm lost on the fact that I believe I have a subform which appears should be on another subform which should then be on a form. If this is correct, the form looks extremely busy and not user friendly, and just plain incorrect. Is it true, based on the relationships, that the PK and FK on the related tables should be joined between the subform and main form? These are the tables I have: TblIncident IncidentnumberID (PK) misc. fields TblSuspect SuspectID (PK) misc. fields TblOfficer OfficerBadgeNumberID (PK) OfficerLastName OfficerFirstName TblIncidentOfficer IncidentOfficerID (PK) IncidentNumberID (FK) OfficerBadgeNumberID (FK) TblIncidentSuspect IncidentSuspectID (PK) IncidentNumberID (FK) SuspectID (FK) TblIncidentSuspectCharges IncidentSuspectChargesID (PK) IncidentSuspectID (FK) ChargesID (FK) TblForceUsed ForceUsedID (PK) OfficerBadgeNumber (FK) TypeofForceID (FK) TblCharges ChargesID (PK) Charges Thanks in advance for any assistance. |
#3
|
|||
|
|||
Confusion with forms and related subforms
Thanks for the quick response - regarding the point you bring up reference
the officerbadgenumber in the forceused table, I had posted my table design a couple weeks ago and based on the fact that there could be more than one officer involved, sometimes using more than one type of force, the tables were set up that way. Hopefully that is correct for that scenario. Your reply makes me wonder if I'm looking at the forms incorrectly. When needed, I expect each of these fields to be used (i.e., there will never be an incident where no officer is involved, no suspect, no type of force, etc.). I guess that's why I'm looking at putting everything on a form for the user to input the data. With your second paragraph, it would seem most forms/subforms are done for people to "view" the data already in the records. Your advice is appreciated and I'll give it a shot. "Daryl S" wrote: Lee Ann - A subform is used to help show the 'many' side of a one-to-many relationship. For many databases, this is a straight-forward relationship. It gets more complicated with the many-to-many relationships such as those in your database. While the theory still holds, you have to adjust it to your needs. For example, you might want to show suspects with all their incidents. The suspect table would be the recordsource for the main form, and Incident information would be used in the subform. This Incident information would normally be a query based on the IncidentSuspect table joined to the Incident table. You could also show Officers on a main form with Incidents on a subform this way, and many other one-to-many relationships. I have a feeling you want to show everything about an incident on one form. In that case, the Incident table would be used for the main form, and you may have several subforms - one for the officers, one for the suspects. The suspect subform would probably have a recordsource of a query based on TblIncidentSuspect joined with TblSuspect, and would be linked to the main form through the IncidentNumberID. This suspect main form may include a subform of its own to show the charges against the suspects. This sub-subform would probably have as its record source a query that joins the tblCharges to the tblIncidentSuspectCharges, and would be linked to the IncidentSuspect subform based on the IncidentSuspectID field. Similarly, the Officer subform might have a subform of it's own to show the Force Used. This data would come from a query based on the the TblForceUsed joined to the TypeOfForce table. Trying to do this will point out that you need to change your TblForceUsed table - instead of OfficerBadgeNumber, you need the IncidentOfficerID as the foreign key. Hope that helps! -- Daryl S "Lee Ann" wrote: Hello, I'm trying to figure out the concept of embedding subforms into main forms. I have main tables, 4 junction tables, and a few look-up tables (not all look-ups are listed below). If I'm reading it correctly, the junction tables which relate to the main tables should be in the form of a subform placed within the main form they relate to? I'm lost on the fact that I believe I have a subform which appears should be on another subform which should then be on a form. If this is correct, the form looks extremely busy and not user friendly, and just plain incorrect. Is it true, based on the relationships, that the PK and FK on the related tables should be joined between the subform and main form? These are the tables I have: TblIncident IncidentnumberID (PK) misc. fields TblSuspect SuspectID (PK) misc. fields TblOfficer OfficerBadgeNumberID (PK) OfficerLastName OfficerFirstName TblIncidentOfficer IncidentOfficerID (PK) IncidentNumberID (FK) OfficerBadgeNumberID (FK) TblIncidentSuspect IncidentSuspectID (PK) IncidentNumberID (FK) SuspectID (FK) TblIncidentSuspectCharges IncidentSuspectChargesID (PK) IncidentSuspectID (FK) ChargesID (FK) TblForceUsed ForceUsedID (PK) OfficerBadgeNumber (FK) TypeofForceID (FK) TblCharges ChargesID (PK) Charges Thanks in advance for any assistance. |
#4
|
|||
|
|||
Confusion with forms and related subforms
Lee Ann -
I think the table does need updating, as it tells what type of force was used by what officer, but it doesn't indicate which incident this is for. You could add the IncidentNumberID to this table as a foreign key if you don't want to switch the OfficerBadgeNumber to the IncidentOfficerID. If you don't do one of these, then when you pull up an Officer, then through his OfficerBadgeNumber you can find all the types of force used, but you won't be able to say which force was used in which incident. As for your other question, for simple relationships you can use the same forms for data entry and for viewing. You can change form properties to allow the form only for adding new records in one case and for only viewing results in another. The fact that a query is the recordsource instead of a table does not change the ability to add, delete, or update records in itself, but some queries will not allow adds or updates, for example to the 'one' side of a one-to-many relationship. This makes sense from a data integrity standpoint. In your complex case, you will need to make some adjustments to straight data-entry of all the data. Normally, from the Incident point of view, you would want to add a new incident on the main form, and after it is saved, you would want to add the officers and suspects to the incident. Normally, you would do this in the Officer and Suspect subforms. You would select each officer from a drop-list based on TblOfficer,each a new record in the subform. Similarly, you would select each suspect from a drop-list based on TblSuspect in the Suspect subform. If the suspect drop-list does not contain the suspect (because this is a new suspect), then you would want to pop up a form to fill out the new suspect's information. This would update the table TblSuspect and when done, it could place the new suspect onto the Suspect subform on the Incident form. Remember that each Officer you entered now needs one or more records added to TblForceUsed, and each IncidentSuspect that you added needs one or more records added to TblIncidentSuspectCharges. This is where the sub-subforms come in... I hope that helps! -- Daryl S "Lee Ann" wrote: Thanks for the quick response - regarding the point you bring up reference the officerbadgenumber in the forceused table, I had posted my table design a couple weeks ago and based on the fact that there could be more than one officer involved, sometimes using more than one type of force, the tables were set up that way. Hopefully that is correct for that scenario. Your reply makes me wonder if I'm looking at the forms incorrectly. When needed, I expect each of these fields to be used (i.e., there will never be an incident where no officer is involved, no suspect, no type of force, etc.). I guess that's why I'm looking at putting everything on a form for the user to input the data. With your second paragraph, it would seem most forms/subforms are done for people to "view" the data already in the records. Your advice is appreciated and I'll give it a shot. "Daryl S" wrote: Lee Ann - A subform is used to help show the 'many' side of a one-to-many relationship. For many databases, this is a straight-forward relationship. It gets more complicated with the many-to-many relationships such as those in your database. While the theory still holds, you have to adjust it to your needs. For example, you might want to show suspects with all their incidents. The suspect table would be the recordsource for the main form, and Incident information would be used in the subform. This Incident information would normally be a query based on the IncidentSuspect table joined to the Incident table. You could also show Officers on a main form with Incidents on a subform this way, and many other one-to-many relationships. I have a feeling you want to show everything about an incident on one form. In that case, the Incident table would be used for the main form, and you may have several subforms - one for the officers, one for the suspects. The suspect subform would probably have a recordsource of a query based on TblIncidentSuspect joined with TblSuspect, and would be linked to the main form through the IncidentNumberID. This suspect main form may include a subform of its own to show the charges against the suspects. This sub-subform would probably have as its record source a query that joins the tblCharges to the tblIncidentSuspectCharges, and would be linked to the IncidentSuspect subform based on the IncidentSuspectID field. Similarly, the Officer subform might have a subform of it's own to show the Force Used. This data would come from a query based on the the TblForceUsed joined to the TypeOfForce table. Trying to do this will point out that you need to change your TblForceUsed table - instead of OfficerBadgeNumber, you need the IncidentOfficerID as the foreign key. Hope that helps! -- Daryl S "Lee Ann" wrote: Hello, I'm trying to figure out the concept of embedding subforms into main forms. I have main tables, 4 junction tables, and a few look-up tables (not all look-ups are listed below). If I'm reading it correctly, the junction tables which relate to the main tables should be in the form of a subform placed within the main form they relate to? I'm lost on the fact that I believe I have a subform which appears should be on another subform which should then be on a form. If this is correct, the form looks extremely busy and not user friendly, and just plain incorrect. Is it true, based on the relationships, that the PK and FK on the related tables should be joined between the subform and main form? These are the tables I have: TblIncident IncidentnumberID (PK) misc. fields TblSuspect SuspectID (PK) misc. fields TblOfficer OfficerBadgeNumberID (PK) OfficerLastName OfficerFirstName TblIncidentOfficer IncidentOfficerID (PK) IncidentNumberID (FK) OfficerBadgeNumberID (FK) TblIncidentSuspect IncidentSuspectID (PK) IncidentNumberID (FK) SuspectID (FK) TblIncidentSuspectCharges IncidentSuspectChargesID (PK) IncidentSuspectID (FK) ChargesID (FK) TblForceUsed ForceUsedID (PK) OfficerBadgeNumber (FK) TypeofForceID (FK) TblCharges ChargesID (PK) Charges Thanks in advance for any assistance. |
Thread Tools | |
Display Modes | |
|
|