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
|
|||
|
|||
Table Relationship issue.
Question Subject: Relationships - I am issing something 11/18/2008 5:01 PM PST By: dwilliamson In: microsoft.public.access.tablesdbdesign Have a database with two tables - tblSite and tblInsp. tblSiteID is PK and tblInspID, tblSiteID is combo key in tblInsp table. This databse is used to track inspection for a given site. So, I need to be able to open the frmSite enter site info, click on the Inspections command button and enter inspections. I have a third table used to join the the tblSite and tblInsp. the tblSite to the join table is one - to - many. The tblInsp to the join table is intermediate. The problem I am having is when i enter an inspection, exit the inspection form, and then return the inspection I just entered is no longer linked to the site. Thanks. |
#2
|
|||
|
|||
Table Relationship issue.
The fact that you have a junction table would imply that you have a
many-to-many relationship between Sites and Inspections, but perhaps that should should be clarified. A given Site could obviously be inspected more than once but , on the surface, it would seem that a given Inspection would normally only apply to one Site. However, your business rules may indeed permit an Inspection to apply to more than one Site, in which case the junction table would be necessary. If the relationship is m:m then your table structure should look something like; tblSites ****** SiteID (PK) SiteName other attributes of each Site tblInspections ********** InspectionID (PK) InspectorID (Foreign Key to a table of Inspectors/Employees, etc.) InspectionDate other attributes of each inspection tblSiteInspections (the junction table) ************* SiteID (FK to tblSites) InspectionID (FK to tblInspections) The junction table would typically use SiteID and InspectionID as a composite PK and your subform would be based on the junction table using combo boxes for data entry. -- _________ Sean Bailey "dwilliamson" wrote: Question Subject: Relationships - I am issing something 11/18/2008 5:01 PM PST By: dwilliamson In: microsoft.public.access.tablesdbdesign Have a database with two tables - tblSite and tblInsp. tblSiteID is PK and tblInspID, tblSiteID is combo key in tblInsp table. This databse is used to track inspection for a given site. So, I need to be able to open the frmSite enter site info, click on the Inspections command button and enter inspections. I have a third table used to join the the tblSite and tblInsp. the tblSite to the join table is one - to - many. The tblInsp to the join table is intermediate. The problem I am having is when i enter an inspection, exit the inspection form, and then return the inspection I just entered is no longer linked to the site. Thanks. |
#3
|
|||
|
|||
Table Relationship issue.
I believe I am finally getting somewhere. You were correct in the statement
that a given site will have many inspections but a particular inspection shall ONLY apply to that site. So a many to many relationship is not required. Where did I go wrong and ideas on how to fix? Derek "Beetle" wrote: The fact that you have a junction table would imply that you have a many-to-many relationship between Sites and Inspections, but perhaps that should should be clarified. A given Site could obviously be inspected more than once but , on the surface, it would seem that a given Inspection would normally only apply to one Site. However, your business rules may indeed permit an Inspection to apply to more than one Site, in which case the junction table would be necessary. If the relationship is m:m then your table structure should look something like; tblSites ****** SiteID (PK) SiteName other attributes of each Site tblInspections ********** InspectionID (PK) InspectorID (Foreign Key to a table of Inspectors/Employees, etc.) InspectionDate other attributes of each inspection tblSiteInspections (the junction table) ************* SiteID (FK to tblSites) InspectionID (FK to tblInspections) The junction table would typically use SiteID and InspectionID as a composite PK and your subform would be based on the junction table using combo boxes for data entry. -- _________ Sean Bailey "dwilliamson" wrote: Question Subject: Relationships - I am issing something 11/18/2008 5:01 PM PST By: dwilliamson In: microsoft.public.access.tablesdbdesign Have a database with two tables - tblSite and tblInsp. tblSiteID is PK and tblInspID, tblSiteID is combo key in tblInsp table. This databse is used to track inspection for a given site. So, I need to be able to open the frmSite enter site info, click on the Inspections command button and enter inspections. I have a third table used to join the the tblSite and tblInsp. the tblSite to the join table is one - to - many. The tblInsp to the join table is intermediate. The problem I am having is when i enter an inspection, exit the inspection form, and then return the inspection I just entered is no longer linked to the site. Thanks. |
#4
|
|||
|
|||
Table Relationship issue.
In that case the junction table is not necessary (at least not for
this relation). Instead, you would have a Site table and an Inspection table with a structure something like; tblSites ****** SiteID (PK) SiteName other attributes of each Site tblInspections ********** InspectionID (PK) SiteID (Foreign Key to tblSites) InspectorID (FK to a table of Inspectors/Employees, etc.) InspectionDate other attributes of each inspection As far as your form design, that would depend on how you want to enter data. You could have a main form based on tblSites with a subform based on tblInspections. The Master/Child link between the forms would be SiteID. With this design you would need to first select a Site and then enter information (in the subform) about the Inspections related to that Site. Another option would be to have one form based on tblInspections and on that form you would use a combo box (bound to the SiteID field) to select the Site for each Inspection. -- _________ Sean Bailey "dwilliamson" wrote: I believe I am finally getting somewhere. You were correct in the statement that a given site will have many inspections but a particular inspection shall ONLY apply to that site. So a many to many relationship is not required. Where did I go wrong and ideas on how to fix? Derek "Beetle" wrote: The fact that you have a junction table would imply that you have a many-to-many relationship between Sites and Inspections, but perhaps that should should be clarified. A given Site could obviously be inspected more than once but , on the surface, it would seem that a given Inspection would normally only apply to one Site. However, your business rules may indeed permit an Inspection to apply to more than one Site, in which case the junction table would be necessary. If the relationship is m:m then your table structure should look something like; tblSites ****** SiteID (PK) SiteName other attributes of each Site tblInspections ********** InspectionID (PK) InspectorID (Foreign Key to a table of Inspectors/Employees, etc.) InspectionDate other attributes of each inspection tblSiteInspections (the junction table) ************* SiteID (FK to tblSites) InspectionID (FK to tblInspections) The junction table would typically use SiteID and InspectionID as a composite PK and your subform would be based on the junction table using combo boxes for data entry. -- _________ Sean Bailey "dwilliamson" wrote: Question Subject: Relationships - I am issing something 11/18/2008 5:01 PM PST By: dwilliamson In: microsoft.public.access.tablesdbdesign Have a database with two tables - tblSite and tblInsp. tblSiteID is PK and tblInspID, tblSiteID is combo key in tblInsp table. This databse is used to track inspection for a given site. So, I need to be able to open the frmSite enter site info, click on the Inspections command button and enter inspections. I have a third table used to join the the tblSite and tblInsp. the tblSite to the join table is one - to - many. The tblInsp to the join table is intermediate. The problem I am having is when i enter an inspection, exit the inspection form, and then return the inspection I just entered is no longer linked to the site. Thanks. |
Thread Tools | |
Display Modes | |
|
|