Results 1 to 8 of 8
  1. #1
    elliemac is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    4

    Joining table?

    hi


    I'm new to Access (and Windows!), trying to set up a basic db for clients / visits in an NFP. I was doing fine until i tried to establish links between clients to indicate family relationships.

    I currently have two tables :

    Table 1: individual client registrations (PK: ClientID, autonumber). The table includes a 'FamilyID' field, (basically one of the family's client ID numbers is manually inserted for each member of the family)

    Table 2. Visits (PK: autonumber, VisitID; FK: Client ID).

    This works to link individual registrations to individual visits, but while we need to have separate registration records for each member of the family, we actually need all visits to be linked to the family not the individual. I get that it's a 'many to many' relationship but i just can't work out how to create the joining table. What fields would i have in the joining table?

    I am tying myself in knots, so all help will be gratefully received.

    thanks
    ellie.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    You need a "Family" table or call it Accounts. Then you need a related dependent table "Registrations" for all individuals associated with that account. So with the "Visits" table that is 3 tables so far.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    elliemac is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    4
    Quote Originally Posted by June7 View Post
    You need a "Family" table or call it Accounts. Then you need a related dependent table "Registrations" for all individuals associated with that account. So with the "Visits" table that is 3 tables so far.
    Thanks. What I can't seem to get right is what fields link the tables.

    'Family ID' in Client Registrations (ie many records) to 'ID (PK,autonumber)' in the Joining table would seem right, but i'm getting a 'referential integrity' error, and from there I question everything i know about Access (and the universe in general). I've uploaded a screenshot below.

    Click image for larger version. 

Name:	Screenshot (13).png 
Views:	18 
Size:	13.0 KB 
ID:	38283

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    elliemac is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    4
    I've attached the db (i hope!). I'm really grateful for your help.CAN_copy.zip

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    There are duplicate FamilyID values in Family and this should be a unique value.

    Then there are FamilyID values in Registration that are not in Family table ID field. This will cause the referential integrity issue. You can track down all these or maybe best to start over from scratch. Delete all records. Then run Compact & Repair on the Database Tools tab. Then establish relationships. Then build forms

    Should not use spaces nor punctuation/special characters in naming convention.

    Also, advise not to use exact same name for multiple fields.

    Have you studied an introductory tutorial on Access and/or relational database?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    elliemac is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    4
    Quote Originally Posted by June7 View Post
    There are duplicate FamilyID values in Family and this should be a unique value.

    Then there are FamilyID values in Registration that are not in Family table ID field. This will cause the referential integrity issue. You can track down all these or maybe best to start over from scratch. Delete all records. Then run Compact & Repair on the Database Tools tab. Then establish relationships. Then build forms

    Should not use spaces nor punctuation/special characters in naming convention.

    Also, advise not to use exact same name for multiple fields.

    Have you studied an introductory tutorial on Access and/or relational database?
    Yes, i have studied introductory tutorials. To be honest, they're like reading a foreign language. I'm limping along slowly.

    Thanks again for your time. I am still at a loss but at this point I think i'll pay someone to create it for me.

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Maybe you would benefit from 'old school' and get a book and start from page 1.

    Here is an on-line tutorial that might be as good as a book https://edu.gcfglobal.org/en/access2...o-databases/1/

    Or sure, pay thousands of dollars for someone to build for you. Not exaggerating, application development can be costly.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 11-28-2016, 08:18 AM
  2. Replies: 1
    Last Post: 07-01-2014, 01:41 PM
  3. Replies: 3
    Last Post: 09-16-2013, 01:05 PM
  4. Replies: 5
    Last Post: 08-06-2013, 01:32 PM
  5. Joining tables two tables and another table that is not
    By DevintheDude in forum Database Design
    Replies: 0
    Last Post: 09-12-2007, 08:56 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