Results 1 to 4 of 4
  1. #1
    Frasse is offline Novice
    Windows XP Access 2000
    Join Date
    Aug 2012
    Posts
    1

    Relations


    I have made a database with 3 tables, A, B and C. I want to setup an 1:1 relation between A and B and an 1:1 relation between A and C?. Is it possible to do that?

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by Frasse View Post
    I have made a database with 3 tables, A, B and C. I want to setup an 1:1 relation between A and B and an 1:1 relation between A and C?. Is it possible to do that?
    I don't think it is. 1:1 relationships are not the most used. I can't imagine would you could be doing to need this. Can you tell us more.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    I sort of do that. Example:

    tblSample
    SampleID (primary key)

    tblTestA
    SampleID (primary key)

    tblTestB
    SampleID (primary key)

    tblTestC
    SampleID (primary key)

    A sample can have any number of different test procedures associated with it but not all of the test procedures and only one of each test procedure. So sample 1010 could have test A and C and sample 1011 could have test B.

    The way this database works, users login a sample and select the desired tests from a list. Code creates the test records with SQL INSERT actions. Then on some later date forms with RecordSource that join the related records are opened for entry of test data. Form/Subforms when needed have Master/Child links on the SampleIDs. Data integrity is managed by code. Code also allows data entry into fields for only the tests that have been selected.

    Relationships can be established in the builder, however, I have so many different test procedures that I cannot setup relationships in the Relationships window (I think limit is like 30 tables). This parent/child linking on two PK fields (neither is autonumber) does seem to work to allow creation of dependent record, I just don't have forms setup that way. Also, the master table (tblSample) will show only one dependent table when clicking the + in the left table margin. But users don't work directly with tables anyway so this is irrelevant.
    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.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Further to what June and Bob have provided/advised, I think we need to know more about your intended data base. Perhaps you could give us some meaningful table names and sample data in order that we can understand your situation. Table A, B and C is nice in theory, but it's a little too abstract to respond with specific ideas.

    Is it possible
    sure, but you may have alternatives if there was more info.

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

Similar Threads

  1. Expressions and Relations Between Queries
    By alexknopp in forum Database Design
    Replies: 1
    Last Post: 05-01-2012, 09:54 AM
  2. Problem with relations
    By PoorCadaver in forum Access
    Replies: 17
    Last Post: 10-18-2011, 12:31 PM
  3. Confusion with table relations
    By BusDriver3 in forum Database Design
    Replies: 13
    Last Post: 08-21-2011, 01:48 PM
  4. one to one relations
    By crackpot in forum Database Design
    Replies: 2
    Last Post: 08-18-2010, 09:39 AM
  5. two relations to one table?
    By kannuberg in forum Forms
    Replies: 19
    Last Post: 09-27-2009, 11:25 AM

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