0

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.

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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