Table Relationship issue.

[dwilliamson]

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.

 

[Beetle]

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.

 

[dwilliamson]

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]

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.