Page 1 of 3 123 LastLast
Results 1 to 15 of 36
  1. #1
    raychow22 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    145

    Relationships With Primary Key to Table with Multiple Foreign Keys

    Hi Forum,

    I am trying to create a relationship for a Table containing LABID that is tied in a INSPECTOR'S NAME (SpecialInspector1 and SpecialInspector2); On my InspectionReport table, there are two fields capturing INSPECTOR'S NAME (correlated to a LABID); I want to be able to create the relationship for both fields so that all Inspection records are tied into the LABID for both fields SpecialInspector1 and SpecialInspector2. See pictures for visuals.




    Click image for larger version. 

Name:	Access Pic2.JPG 
Views:	57 
Size:	29.8 KB 
ID:	44456

    Click image for larger version. 

Name:	Access Pic1.JPG 
Views:	51 
Size:	193.2 KB 
ID:	44455

    Click image for larger version. 

Name:	Access Pic3.JPG 
Views:	50 
Size:	109.9 KB 
ID:	44457

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Just add the table twice in the Relationships window and join SpecialInspector1 and SpecialInspector2 to LabID in each instance of the table. I think you have those fields (SpecialInspector) set as lookup fields, I would recommend against that as you'll probably run into problems in the future. See http://access.mvps.org/access/lookupfields.htm for more info on this.

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    raychow22 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    145
    Vlad,

    This is what I got from your last response. It didn't work. The qrLABID are to use their respective LABID Associated to tblLABnames.
    Attached Thumbnails Attached Thumbnails Picture1.JPG  

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    That is not what I said. You want to add the tblLABnames table twice and only one LABIDSpecialINspector. Then you join SpecialInspector1 field to tblLABnames.LABID and you join SpecialInspector2 field to tblLABnames.LABID. You might get an error for mismatched data types and that is caused by having the fields defined as lookup at the table level, as you probably have them set up as text but thei are actually storing the LABIDs. Read the explanation in the link I provided earlier.

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    raychow22 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    145
    Vlad,

    I created columns for Special Inspector's LabIDs (numbers) correlated to the tblLABnames (numbers). It still does not show any records.
    Attached Thumbnails Attached Thumbnails Picture1.JPG  

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Are you doing that in a query or in the relationships window as your title suggests? What is the purpose of the query? You do not need to add the new fields. Instead remove the lookups from the original fields as you don't need them in tables (you use a combo to do in forms)., change the data type from text to number and link the fields directly to the LABIds from the two intances of the table. If you do this in a query you will only get those records that have both specialinspectors populated. If you want to show all records change the two joins to outer joins (with the arrows pointing towards the LABids). Maybe you can upload a sample (no sensite data, just a couple "dummy"records to show the issue).
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    raychow22 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    145
    Vlad,

    Thanks for taking your time to help me out. Attached is a sample dB. Another issue i'm running into is MVF in which I want to move away from but in order to do so I need create multiple fields for them. But first things first, let's try to make sense of the relationships with multiple "same data" fields.

    Thanks again,
    Attached Files Attached Files

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Sorry but I can't use your sample, the tblInspectionReports does not open, can you please import the table again and run a compact and repair?

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  9. #9
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    To replace MVF you don't need multiple fields as that would go against good relational dataabase design\normalization rules. What you want is a new join or linking table.
    For example you can replace all those Yes\No fields you have in tblLABnames with a new table called tblLABcertification with these fields:
    LABCertID (autonumber, PK)
    LABidFK (Number long, FK for tblLABid)
    CertificationTypeFK (Number long, FK for new table tblCertificationType)

    Now create a lookup table for the certification types (tblCertificationTypes)
    CetificationTypeID (autonumber, PK)
    CertificationType (Short Text)

    In your forms use a combo bound to CertificationTypeFK with its row source based on tblCertificationType to display the certification type descrption but store the actual ID.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  10. #10
    raychow22 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    145
    Quote Originally Posted by Gicu View Post
    Sorry but I can't use your sample, the tblInspectionReports does not open, can you please import the table again and run a compact and repair?

    Cheers,
    Vlad
    How about now?
    Attached Files Attached Files

  11. #11
    raychow22 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    145
    Quote Originally Posted by Gicu View Post
    To replace MVF you don't need multiple fields as that would go against good relational dataabase design\normalization rules. What you want is a new join or linking table.
    For example you can replace all those Yes\No fields you have in tblLABnames with a new table called tblLABcertification with these fields:
    LABCertID (autonumber, PK)
    LABidFK (Number long, FK for tblLABid)
    CertificationTypeFK (Number long, FK for new table tblCertificationType)

    Now create a lookup table for the certification types (tblCertificationTypes)
    CetificationTypeID (autonumber, PK)
    CertificationType (Short Text)

    In your forms use a combo bound to CertificationTypeFK with its row source based on tblCertificationType to display the certification type descrption but store the actual ID.

    Cheers,
    Hmm.. I really want to learn how to do this.

    Right now there are multi-value fields for up to 8 selected data that is entered in a single field. So, we don't want to create multiple fields (up to 8) in our parent table? How else will each data be linked to that specific record. How will the form look to enter multiple data related to multiple fields?

  12. #12
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Still no luck! Can you please save the file as Access 2007-2013 format (I have 2013)?

    Click image for larger version. 

Name:	Capture.PNG 
Views:	37 
Size:	72.2 KB 
ID:	44491
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  13. #13
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @Vlad,
    Maybe you can open this??
    Attached Files Attached Files

  14. #14
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Much appreciated Steve, unfortunately still no luck:
    Click image for larger version. 

Name:	Capture.PNG 
Views:	33 
Size:	86.8 KB 
ID:	44495

    I take it you are able to open that table, so I'm afraid I don't know why is doing that.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  15. #15
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    OK, the attached zip has 3 copies of the dB, each db has 1 table. There is a A2016 feature in 1 or more tables that is stopping me from converting to A2000 format. Can you open any of the 3 dBs??
    Attached Files Attached Files

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 14
    Last Post: 10-24-2018, 04:37 AM
  2. Replies: 20
    Last Post: 01-09-2017, 12:08 AM
  3. Help allocating Primary/Foreign Keys and Relationships
    By rosscortb in forum Database Design
    Replies: 2
    Last Post: 07-08-2015, 08:12 AM
  4. Replies: 11
    Last Post: 06-11-2012, 12:23 AM
  5. Primary/foreign keys + relationships
    By tripptofer in forum Database Design
    Replies: 0
    Last Post: 08-12-2009, 07:44 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums