Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Table Linking, needed? (2k)

    Hi, I'm trying to help out a friend who is having table problems.

    He has 2 tables, 1 is a static table where the information in it doesn't change.
    -The static table (table 1) contains 100 or so different number codes, each code having additional information in the following columns describing the code.
    -The other table (table 2) which gets updated, has many items which have a key field that doesn't relate to the static table, it relates to another parent table.
    Table 2 has 10 columns that each can store a number code that can be found in table 1. Eventually, he wants to have a report that displays the key field from table 2, and then breaks down each number code into table 1's information (basically the definition of each code).

    Should he be linking his tables? If so, what type of links?

    Note: Multiple columns from table 2 refer back to table 1's information. I have heard of people having problems referring to the same table multiple times

    if I'm not being clear enough, please let me know and I will try to clarify. Thanks!
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  2. #2
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table Linking, needed? (2k)

    Apart from confirming that the data which is entered into table2 exists in table1 (you could use a combo box or list box to check this), i don't believe there needs to be a link.
    You can use a crosstab query to extract the data you require.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Table Linking, needed? (2k)

    I don't know enough about the design and purpose of the tables, but when I read a description like this, I cannot help but wonder whether it is the ideal design. I would probably not put 10 fields in Table2 that each relate to Table1, but create a third, intermediary table with only two fields: one relating to the key field in Table1, the other relating to the key field in Table2.

    If your firend prefers to keep the current design, it is very well possible to create multiple relationships to the same table. I use it in several databases without problems. The idea is to add Table1 10 times to the relationships window, and to link each of the 10 code fields in Table2 to a different instance of Table1. If you link two fields in Table2 to the same instance of Table1, you will have problems indeed, so that is to be avoided.

    The advantage of linking the tables is that you can enforce referential integrity - the user won't be able to enter a non-existing code in Table2.

  4. #4
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table Linking, needed? (2k)

    Thanks to both of you. BTW, I agree with you Hans on the table not being the ideal design, but he's new to access and I don't have the time to make the database for him. I can only advise that he re-think the way his data is organized. I'll be sure to tell him how to do it the way you suggested though, if he does decide to keep the information ordered the way it is.

    But out of curiousity, if he used an intermediary table, would both fields in that table be key fields?
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  5. #5
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table Linking, needed? (2k)

    interesting, I'll mention that to him, thanks Hans!
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Table Linking, needed? (2k)

    The combination of the two fields would be the primary key in the intermediary table - at least, if the combinations should be unique. The attached screenshot shows how a composite primary key is constructed: the key name occurs only in the first row.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •