userFK twice in the same table

[Misiek]

Hello,

I have a table DEFECTS and table USERS

in the table Defects is a field called dUserFK which is linked to table Users. I need to make some changes to layout of the database.
Until now, we had PERSON REPORTING which is dUserFK, but need to add another field in the same table PERSON INVESTIGATING, I added another field to table DEFECTS called dUser2FK.
When I run query based on the table DEFECTS it doesnt give any results, if I remove relationship between dUser2FK and table USER then everything work as it used to.

I understand there must be a conflict of repeated data, how to go around it please?

 

[arnelgp]

on your query design drag 2 User table in the table panel, now Left Join dUserFK field in the first User table, then Left Join dUserFK2 to the other user table.

 

[Misiek]

it didnt work, but when Left joiun first one and right join second one it works.
Is that correct?

 

[arnelgp]

both Left Joins, you should have 3 tables there in your query design:
1. Defects
2. Users
3. Users_1

Defect Left Join User On Defect.dUser = User.UserID
Defect Left Join User_1 On Defect.dUser = User_1.UserID

you should also modify your relation, same as the above setup.

 

[Misiek]

yep, that worked lovely.
Thank You