Ms-access – Non unique one to many relationship

database-designms access

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?

Best Answer

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)
) ;
Related Question