A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Table Relationship issue.



 
 
Thread Tools Display Modes
  #1  
Old November 19th, 2008, 04:14 PM posted to microsoft.public.access.tablesdbdesign
dwilliamson
external usenet poster
 
Posts: 7
Default 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  
Old November 19th, 2008, 05:59 PM posted to microsoft.public.access.tablesdbdesign
Beetle
external usenet poster
 
Posts: 1,254
Default 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  
Old November 19th, 2008, 06:58 PM posted to microsoft.public.access.tablesdbdesign
dwilliamson
external usenet poster
 
Posts: 7
Default 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  
Old November 19th, 2008, 11:13 PM posted to microsoft.public.access.tablesdbdesign
Beetle
external usenet poster
 
Posts: 1,254
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:33 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.