SQL Access: Multiple Inner Join between Two Tables

Question

I'm working in Microsoft Access.

I have two tables, Production and Reagents.

Reagents
- Reagent Reference ID (text)
- Supplier (text)

Production
- Production Reference ID (text)
- C Reference ID (text) (matches Reagents.Reagent Reference ID)
- P Reference ID (text) (matches Reagents.Reagent Reference ID)

I'm trying to build a query to link the Supplier of C and P (from Reagents) to the Production Reference ID (from Production), as below.

Query Output
- Production Reference ID
- C Supplier
- P Supplier

Below is what I've got so far, but it tells me I'm missing an operator.

SELECT C.Supplier as 'C Supplier', P.Supplier as 'P Supplier'
FROM Production
INNER JOIN Reagents AS C ON C.Reagent Reference ID=Production.C Reference ID
INNER JOIN Reagents AS P ON P.Reagent Reagent Reference ID=Production.P Reference ID
WHERE Production.Production Reference ID=?

Thanks in advance!

C Reference ID would be along the lines of C00001, while P Reference ID would be along the lines of P00001, and P and C will never match. Both P Reference ID and C Reference Reference IDs are included under the Reagent Reference ID. — James Boxall
– James Boxall, Commented Jan 31, 2017 at 10:54
Not sure if you strictly need a join, but I gave a solution using subqueries below. I tested it with a similar table on my own access database, and it worked. Beware of slowness if you exceed a couple thousand records and have no where conditional though — Magisch
– Magisch, Commented Jan 31, 2017 at 10:56

Magisch · Accepted Answer · 2017-01-31 12:01:11Z

2

You can try this method (this is using subqueries, beware it may be somewhat slow if the table is big):

SELECT Production.[Production Reference ID],
(SELECT Reagents.Supplier FROM Reagents WHERE Reagents.[Reagent Reference ID] = Production.[C Reference ID] ) as CSupplier, 
(SELECT Reagents.Supplier FROM Reagents WHERE Reagents.[Reagent Reference ID] = Production.[P Reference ID] ) as PSupplier
FROM Production;

Edit: With some Testing, I found out how it works in JOIN too, and it's a lot faster then method 1:

SELECT C.Supplier as 'C Supplier', P.Supplier as 'P Supplier'
FROM ( Production
INNER JOIN Reagents AS C ON C.[Reagent Reference Number]=Production.[C Reference Number] )
INNER JOIN Reagents AS P ON P.[Reagent Reagent Reference Number]=Production.[P Reference Number]
WHERE Production.[Production Reference Number]=?

MS Access requires parentheses when using more then one JOIN in a query.

edited Jan 31, 2017 at 12:01

answered Jan 31, 2017 at 10:51

Magisch

7,3529 gold badges40 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

James Boxall Over a year ago

This works perfectly for me - the tables are never going to get too big, so this should work well for it for the foreseeable future. Many thanks!

Magisch Over a year ago

@JamesBoxall Keep in mind you can accept one of the answers to indicate that it worked for you by clicking the green checkmark next to the votes. Also I think Stefan's answer will work if you put proper parentheses (access is finnicky with these).

Magisch Over a year ago

@JamesBoxall I figured out a JOIN version too, was lacking parentheses. This one is faster, too.

James Boxall Over a year ago

Thanks for your help! First time asking questions on SO, but I have now accepted the answers! :)

Magisch Over a year ago

@JamesBoxall You can only accept one answer, not multiple "answers".

Stefan · Accepted Answer · 2017-01-31 10:51:17Z

0

Try encapsulating your fieldnames on brackets[], like below.

SELECT C.Supplier as 'C Supplier', P.Supplier as 'P Supplier'
FROM Production
INNER JOIN Reagents AS C ON C.[Reagent Reference Number]=Production.[C Reference Number]
INNER JOIN Reagents AS P ON P.[Reagent Reagent Reference Number]=Production.[P Reference Number]
WHERE Production.[Production Reference Number]=?

answered Jan 31, 2017 at 10:51

Stefan

1873 silver badges15 bronze badges

3 Comments

James Boxall Over a year ago

This looks to me like it should work, but it gives me the error: Syntax error (missing operator) in query expression 'C.[Reagent Reference Number]=Production.[C Reference Number] INNER JOIN Reagents AS P ON P.[Reagent Reagent Reference Number]=Production.[P Reference Number'.

Magisch Over a year ago

You have a common mistake in there. MS Access requires parentheses when using more then one JOIN.

James Boxall Over a year ago

I needed brackets around my first inner join, as below: FROM (Production INNER JOIN Reagents AS C ON C.[Reagent Reference Number]=Production.[C Reference Number]) INNER JOIN ... With the brackets in place, this works great! - Apologies Magisch, only just noticed your comment!

Collectives™ on Stack Overflow

SQL Access: Multiple Inner Join between Two Tables

2 Answers 2

5 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related