Join multiple columns ?

  • Thread starter Thread starter Don
  • Start date Start date
D

Don

(Sorry if previously asked and answered, I did look..)

How can I join many-to-one columns in jet?

For example, two tables named Staff and Titles. Staff may have 2
titles.

So, Staff:
Name Title1ID Title2ID
John 1 2
Mary 1 4
Chris 2 3

Titles:
TitleID Title
1 Center
2 Guard
3 Goalie
4 Defenseman
5 Attackman

I want:
Name Title1 Title2
John Center Guard
Mary Center Defenseman
Chris Guard Goalie

I tried
SELECT a.name,b.title as title1,c.title as title2
FROM staff a
INNER JOIN titles b on a.title1id=b.titleid
INNER JOIN titles c on a.title2id=c.titleid

with no luck, I get
Syntax error (missing operator) in query expression
'a.title1id=b.titleid INNER JOIN titles c on a.title2id=c.titleid'.

Any help would be appreciated. I'm sure it's something dumb because
that's what I'm really good at.

Don Changer
Baltimore, MD
 
I tried
SELECT a.name,b.title as title1,c.title as title2
FROM staff a
INNER JOIN titles b on a.title1id=b.titleid
INNER JOIN titles c on a.title2id=c.titleid

with no luck, I get
Syntax error (missing operator) in query expression
'a.title1id=b.titleid INNER JOIN titles c on a.title2id=c.titleid'.
Click to expand...

Access is very finicky about parentheses. Try

SELECT a.name,b.title as title1,c.title as title2
FROM (staff a
INNER JOIN titles b on a.title1id=b.titleid)_
INNER JOIN titles c on a.title2id=c.titleid
 
Back
Top