2

I am a noob to database design so bear with me. I have two tables in Microsoft Access:

Table 1:

| ID | Field1 | Field2 |
|:---|--------|:-------|
| 1  |   A    |    1   |
| 2  |   A    |    2   |
| 3  |   B    |    5   |
| 4  |   C    |    8   |
| 5  |   D    |   20   |
| 6  |   E    |   32   |
| 7  |   F    |   22   |
| 8  |   F    |   7    |

Table 2:

| ID | Field1 | Field2 |
|:---|--------|:-------|
| 1  |   A    |  one   |
| 2  |   B    |  two   |
| 3  |   B    | eight  |
| 4  |   D    | seven  |
| 5  |   D    | twenty |
| 6  |   F    | thirty |

I am trying to establish a one to many relationship between the tables based on Field1. However Field1 is not unique in either table. This is conceptually possible, however, because each record in Field1 does have a corresponding record in Field2. I could create an intermediate table containing just the ID's as such

| ID |   ID1  |  ID2   |
|:---|--------|:-------|
| 1  |   1    |   1    |
| 2  |   2    |   1    |
| 3  |   3    |   2    |
| 4  |   3    |   3    |
| 5  |   5    |   4    |
| 6  |   5    |   5    |
| 7  |   5    |   5    |
| 7  |   7    |   6    |
| 7  |   8    |   6    |

However this would be too tedious since I have thousands of records (above tables are just a simplified example). Is there a way in Access to relate these tables directly without having to worry about the ID's? I know I used to be able to do this in ArcGIS using the "Relate Tables" feature so hopefully theres a way to do it in Access?

4
  • 2
    If you had this intermediate table, would you (need to) store extra data in it (have extra columns besides the ID1 and ID2)? Jun 13, 2016 at 12:50
  • No not really. The data I'm interested in is in Table1 and Table2 (Field2, specifically)
    – user32882
    Jun 13, 2016 at 12:53
  • 1
    You don't have to declare a FK. You could create the table and use it in a join. There would be no protection that you entered an valid value. Or you could just have a separate table of valid values for Field1 but then you would need to maintain that table.
    – paparazzo
    Jun 13, 2016 at 12:58
  • 2
    If you want to literally map those values then I think the answer from ypercube is correct.
    – paparazzo
    Jun 13, 2016 at 13:03

2 Answers 2

7

What you describe is not a one-to-many relationship but a many-to-many relationship because the column Field1 is not unique in any of the two tables (and please pick better names to describe the columns and tables, "Table1", "Table2", "Field1" say nothing about the table or the column and are very confusing).

I see two options depending on what your requirements are:

  1. If the relationship between rows of tables 1 and 2 is only inferred by matching the "Field1" values and you don't need to store any more info on that many-to-many relationship, then you don't need any extra table at all. The 2 columns (Field1) in the respective tables are enough to find the related data. You could use a simple join on the two tables:

    SELECT 
        a.id AS ID1,
        b.id AS ID2
        -- any additional column needed from any of the 2 tables
    FROM table1 AS a
      INNER JOIN table2 AS b
        ON a.Field1 = b.Field1 ;
    
  2. If the relationship between rows of tables 1 and 2 is not inferred by matching the "Field1" values (but there are extra conditions/requirements) or you need to store extra info about the relationship, you need an intermediate table. To make sure (enforce) that only rows with same "Field1" are actually related in this table, the common column ("Field1") has to be added, too, and participate in the two FOREIGN KEY constraints. (you will also need UNIQUE constraints on (Fieldd1, ID), on each of the 2 tables, for the foreign keys to work):

    CREATE TABLE intermediate
      ( Field1 VARCHAR(5),
        ID1    INT NOT NULL,
        ID2    INT NOT NULL,
        -- possible extra columns
        PRIMARY KEY (ID1, ID2),
        FOREIGN KEY (Field1, ID1)
          REFERENCES table1 (Field1, ID),
        FOREIGN KEY (Field1, ID2)
          REFERENCES table2 (Field1, ID)
    ) ;
    
3
  • Item 1 might have been a solution but MS-Access only allows inner joins and this wouldn't solve my issue. I tried running it using inner join and got an empty set
    – user32882
    Jun 15, 2016 at 1:47
  • The code in answer is an INNER JOIN. I had forgot that Access requires to explicitly state the INNER. You claim that you run that query and got 0 rows as result? This would be really unexpected and weird. Jun 15, 2016 at 6:50
  • My bad, I had a typo: ON a.Field1 = b.Field2. Corrected to ON a.Field1 = b.Field1. Jun 15, 2016 at 7:04
4

What you appear to have is two tables that refer to a common field
They have an inferred relationship

In the case of a common field then create a master table for Field1 that is nothing but the valid values for Field1. Table1 and Table2 will each have references to the table Field1.

If you really need a direct relationship from Table1 to Table2 then you probably have a faulty data design. When you are in an odd spot like this often it is because you should not you should not be in this spot. Consider posting a general design question for an alternate design.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.