Ms-access – Linking tables in Access with unique ID

database-designms accessms-access-2016

I am creating a database in MS Access that has 3 different tables with 3 separate types of information for one set of users, Personal, Business and Board.

I have one table created with all the users and a unique ID for each.

How do I link up the other 3 tables with the unique ID so the correct information is linked up to each user in each of the tables?

I understand setting relationships, but can't figure out to what field I link up the ID field in the users table to the other tables.

Best Answer

Each table should have a UserID column, used to "link", or relate, rows in the User table with rows in the Personal, Business, and Board tables.

In SQL language, this looks like:

CREATE TABLE Users
(
    UserID int PRIMARY KEY
    , UserName varchar(30)
);

CREATE TABLE Personal
(
    PersonalID int PRIMARY KEY
    , UserID int FOREIGN KEY Users(UserID)
    , PersonalDetails varchar(40)
);

CREATE TABLE Business
(
    BusinessID int PRIMARY KEY
    , UserID int FOREIGN KEY Users(UserID)
    , BusinessDetails varchar(40)
);

CREATE TABLE Board
(
    BoardID int PRIMARY KEY
    , UserID int FOREIGN KEY Users(UserID)
    , BoardDetails varchar(40)
);

Then, when you want to query those tables, you'd write something like:

SELECT UserName
    , PersonalDetails
    , BusinessDetails
    , BoardDetails
FROM Users
    INNER JOIN Personal ON Users.UserID = Personal.UserID
    INNER JOIN Business ON Users.UserID = Business.UserID
    INNER JOIN Board ON Users.UserID = Board.UserID
Related Question