MS Access Inner Join On 3 Tables with the same field_name

Question

I'm doing an assignment for class, and I'm at my whits end. Basically, I need to write a query that uses INNER JOIN's to combine all the data in 4 tables, while avoiding having titles columns with identical names (Ie Table1.Master_Number and Table2.Master_Number). The literal instructions are as follows:

Step 1: Create a view of all the columns (only list the columns once if the columns are duplicated in the tables) in all the tables of the Lecture 5 database. Save this query as Lecture_5_View and us this query to build the following 3 queries. In the “FROM” clause, you MUST use an “INNER JOIN” in this query. Remember ACCESS does not support the “CREATE VIEW” command so you must create a select query. `

Here is a screenshot of the database, that shows all the headings and column headings. Tables

Based on other posts like this, I thought that it would be pretty simple. I have this so far, but it does not want to run

(ie Syntax error(missing operator) in query expression 'Table1.Master_Number = Table2.Master_Number INNER JOIN Table4 ON Table1.Master_Number = Table4.Master_Number)

SELECT * 
FROM Table1
INNER JOIN Table2 ON Table1.Master_Number = Table2.Master_Number
INNER JOIN Table4 ON Table1.Master_Number = Table4.Master_Number

Where am I going wrong so far and how to I accomplish this query?

Much thanks, Josh

What does `does not want to run.` mean? Does it give you an error? — crthompson, Apr 24 '14 at 16:32
And never again use select *. Very bad practice especially if you have joins. PLus you need to list the columns if you want to pass the instructions not to repeate columns. — HLGEM, Apr 24 '14 at 16:37

score 1 · Accepted Answer · edited Jul 29 '20 at 18:27

With Access you need to use parentheses when doing more than one join:

SELECT  * 
FROM    (Table1 
        INNER JOIN Table2 
            ON Table1.Master_Number = Table2.Master_Number)
        INNER JOIN Table4 
            ON Table1.Master_Number = Table4.Master_Number;

This is essentiall spliting down you query so you have at most one join per section, i.e. Your First query is:

SELECT  *
FROM    Table1
        INNER JOIN Table2
            ON Table1.Master_Number = Table2.Master_Number;

Then you have:

SELECT  *
FROM    YourFirstQuery
        INNER JOIN Table4 
            ON Table1.Master_Number = Table4.Master_Number;

This differs slightly from subqueries as you are still able to reference all fields from Table1 and Table2.

EDIT

To avoid duplicating columns you will need to explicitly list the columns you want (although you should be doing this anyway):

SELECT  Table1.Master_Number,
        Table1.Asset_Tag,
        Table1.Serial_Number,
        Table2.Last_Name,
        Table2.First_Name,
        Table4.Office_Number,
        Table4.Location,
        Table4.Department
FROM    (Table1 
        INNER JOIN Table2 
            ON Table1.Master_Number = Table2.Master_Number)
        INNER JOIN Table4 
            ON Table1.Master_Number = Table4.Master_Number;

That worked, but it gives me `Table1.Master_Number`, `Table2.Master_Number`, and `Table4.Master_Number`. I think its supposed to be just one column named `Master_Number`. — mandelbug, Apr 24 '14 at 16:38
I thought I would have to explicitly list them, but I wasn't entirely sure. Thanks for the clarification. Lastly, I would need another `INNER JOIN` for `Table1.Asset_Tag` and `Table3.Asset_Tag`, correct? — mandelbug, Apr 24 '14 at 16:46
Yes, you would just need to add another set of parentheses around the joins. — GarethD, Apr 24 '14 at 18:07

score 0 · Answer 2 · answered Apr 24 '14 at 16:46

0

Try this:

  SELECT * FROM ((Table1
  INNER JOIN Table2 
  ON Table1.Master_Number = Table2.Master_Number)
  INNER JOIN Table4 
  ON Table1.Master_Number = Table4.Master_Number)

answered Apr 24 '14 at 16:46

Olesya Razuvayevskaya

1,148
1
7
10

MS Access Inner Join On 3 Tables with the same field_name

2 Answers2