Querying two foreign key values that are related to the same table?

[thadius856]

I'm trying to run a query from my main table which has two foreign keys that point back to my users table. Since I want to see the user's real name in the query result, I added their NameLast and NameFirst fields to the query.



Access doesn't seem to like this query. It renames the 1st instance of NameLast and NameFirst, then disregards looking up the value to the 2nd instance and just repeats the same data. So I can't get the info for the 2nd user.





I need this functionality because users will be issuing cataloged items to each other. This is to track who gave what to whom. The only two alternatives would be to split each transaction into two halves (but that extends the number of transaction types I would need) or to split my users table into two separate tables (but that would duplicate the data in two places).



In case it helps, here's some screenshots of my database. Hopefully it makes responding a little easier.

tblTransactions:



tblUsers:


Relationships:


SQL:

SELECT tblTransactions.TransactionID, tblTransactions.MediaItemId, tblTransactions.TransactionTypeId, tblTransactions.TransactionDate, tblTransactions.ActorUserId, tblUsers.NameLast, tblUsers.NameFirst, tblTransactions.TargetUserId, tblUsers.NameLast, tblUsers.NameFirst, tblTransactions.TransactionNotesFROM tblTransactionsTypes INNER JOIN (tblUsers INNER JOIN tblTransactions ON tblUsers.UserID = tblTransactions.[ActorUserId]) ON tblTransactionsTypes.TransactionTypeID = tblTransactions.TransactionTypeId
WHERE (((tblTransactions.TransactionTypeId)<=7))
ORDER BY tblTransactions.TransactionDate DESC;

[pbaldy]

You have to use the second instance for one of the groups. You've used tblUsers twice. You'll also want to alias the fields; in design view:

ActionUserLast: NameLast

[thadius856]

Ahhhhh. That makes too much sense! I had to add the table twice with "Show Table", and then delete one of the relationships. I was worried it would delete it for the whole database, but it only deleted the join. I noted the one I deleted didn't have the "one-to-many" symbols graphically. I then just dragged a new join to the second table.

I went ahead and gave all of my fields aliases so that I could use the Column Heads and have it look nice.

I also concatenated 3 fields (1 of them truncated). Takes up so much less space now to display!

For somebody finding this from Google some day, here's what I ended up with:


The long ones end up truncating down to:
Sgt J. Public
Pfc J. Snuffy

[pbaldy]

Glad it worked and welcome to the site!