inner join in access problem 1

[ric2020]

Hi,

I am trying to join two tables to produce a report. A Todo table has two fileds that reference the user table. I can successfully join on one field.

SELECT Users.User, ToDo.*
FROM todo INNER JOIN Users ON (Users.UserID = ToDo.Closedby)

But trying to join on both fields i have problems. I have setup the relationships correctly i think. When i try to use inner join access only selects the records that have been opend and closed by the same person.

SELECT Users.User, Users.User, ToDo.*
FROM Todo INNER JOIN Users ON (Users.UserID = ToDo.Closedby) AND (Users.UserID = ToDo.Createdby);

Any help appreciated.

 

[GingerR]

What are you trying to accomplish?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PHV]

You need 2 instances of the Users table:
SELECT A.User AS Creator, B.User AS Closer, ToDo.*
FROM Todo (INNER JOIN Users A ON ToDo.Createdby=A.UserID)
INNER JOIN Users B ON ToDo.Closedby=B.UserID;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ric2020]

Thanks for the quick replay.

I need the result to simply print a report of all todos with the userid's replaced with username.

I have tried the suggestion from PHV, but unfortunately i get, 'syntax error in from clause'. i have tried various brackets, but no success.

 

[PHV]

Can you please post the SQL code raising the 'syntax error in from clause' ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ric2020]

Ive tried

SELECT A.Users AS Creator, B.Users AS Closer, ToDo.*
FROM Todo (INNER JOIN Users A ON ToDo.Createdby=A.UserID)
INNER JOIN Users B ON ToDo.Closedby=B.UserID;

My two tables are as follows:-

Users
UserID as Autonumber
Name as text

Todo
Createdby = userID , as number
Closedby = userID , as number
...

 

[PHV]

Try this:
SELECT A.Name AS Creator, B.Name AS Closer, ToDo.*
FROM Todo (INNER JOIN Users A ON ToDo.Createdby=A.UserID)
INNER JOIN Users B ON ToDo.Closedby=B.UserID;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ric2020]

Still get 'Syntax error in FROM clause'


I'm using access 2002 on XP, if that helps?

 

[PHV]

My bad, sorry for the typo:
SELECT A.Name AS Creator, B.Name AS Closer, ToDo.*
FROM (Todo INNER JOIN Users A ON ToDo.Createdby=A.UserID)
INNER JOIN Users B ON ToDo.Closedby=B.UserID;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ric2020]

Superb, All works great.

A Million Thanks,

Rick