Multiple relations of 1 table to another with different meanings

[Bleekscheetje]

Hi,

I'm quite new to access and I'm stuck at trying to link records from my table [Users] to different fields of another table [Projects], where these fields have different meaning...

Example:

Table [Users]:
- UserID (primary key)
- Name
- Department
- ...

Table [Projects]:
- ProjectID (primary key)


- Title
- OpenedBy (many to 1 link with Users)
- AssignedTo (many to 1 link with users)
- ...

The problem I'm having here is, that I cannot build queries with field "Name" from Table Users when OpenedBy and AssignedTo are a different user, as access doesn't know which one i'm interested in.

Next, it gets even worse as I add a table [Actions] to the mix, where Actions is linked many to 1 with Projects and each Action is again linked to a User...

How can I resolve this, so that my queries can display the field "Name" instead of fields "OpenedBy" and AssignedTo"?
I don't suppose creating duplicates from the Users table is a good solution as it would take up space and when a new user gets added, they need to get added to multiple tables instead.

Any help is much appreciated!

[ranman256]

if you are querying on 2 tables with the same name, all you do is , put the FULL path to the field: tablename.fieldName.
Also label these fields for a better description:
UserName: tUsers.Name, ClientName: tClients.Name

[Bleekscheetje]

Hello Ranman256,
Thank you for the reply. Unfortunatly what you describe is not what I was having problems with, probably I wasn't clear enough.
Luckily while trying to work out an example I figured out my problem:

When I was building the Query, I loaded both tables into it, using fields like ProjectID from one and Name from the other. This caused the problem as these 2 tables have more than 1 relation between them.
After I removed the table Users from the query and based its results on OpenedBy and AssignedTo, while having those fields in the table itself display the "Name" , my problem seems to be solved.