How to join two tables with relationship (1 Viewer)

isladogs · Jan 21, 2018

Alhakeem1977 said:
Yes, I did.
Your attachment is beyond my knowledge in MS Access.
I will try to add some fields in the table tblFacilityRegister, and I will let you know how it helps.

Thanks a lot for your kind efforts.

All you need to do is copy & paste the query SQL into your database!

Code:

SELECT tblFacilityRegister.ID, tblFacilityRegister.SenderID, tblFacilityRegister.ReceiverID, tblFacilityRegister.ScannedBy, [tblSenders].[FirstName] & ' ' & [tblSenders.LastName] AS SenderName, [tblReceivers].[FirstName] & ' ' & [tblReceivers].[LastName] AS ReceiverName, [tblScanners].[FirstName] & ' ' & [tblScanners].[LastName] AS ScannedByName
FROM ((tblUsers AS tblSenders INNER JOIN tblFacilityRegister ON tblSenders.UserID = tblFacilityRegister.SenderID) INNER JOIN tblUsers AS tblReceivers ON tblFacilityRegister.ReceiverID = tblReceivers.UserID) INNER JOIN tblUsers AS tblScanners ON tblFacilityRegister.ScannedBy = tblScanners.UserID;

What's difficult about that?
Here's the same query in design view

The 3 tables tblSenders, tblReceivers, tblScanners are 3 copies of tblUsers where I've used an alias to distinguish them

Pat Hartman · Jan 22, 2018

If all three FK fields are defined as required, this query will be fine but if they are not defined as required, then the joins need to be left joins.

isladogs · Jan 22, 2018

If all three FK fields are defined as required, this query will be fine but if they are not defined as required, then the joins need to be left joins.

I'd already stated about using outer vs inner joins back in posts 2 & 5.
However, as the OP has decided its all too difficult, it makes no difference anyway!

Pat Hartman · Jan 22, 2018

It was so long ago I forgot

Alhakeem1977 · Jan 24, 2018

Thank you all for your responses, I got it solved by added Users to the Relationships window three times as " The_Doc_Man " recommended, then I did the same in the query too but I changed the Join Properties option 3.

Thanks again to all for interactions, specifically " ridders " I tried the code you provided I got some issues I do not know from where actually but I if I built my database perfectly it will work for sure.

Best Regards,
Alhakeem1977

isladogs · Jan 24, 2018

Glad you got it solved .... but what you did sounds exactly like the solution I provided earlier - see post 21

Alhakeem1977 · Jan 24, 2018

Glad you got it solved .... but what you did sounds exactly like the solution I provided earlier - see post 21

Thanks, I see I'm new in MS Access that's why I can't understand many concepts, you are so advanced.

Thank you very much.

How to join two tables with relationship (1 Viewer)

isladogs

MVP / VIP

Attachments

Pat Hartman

Super Moderator

isladogs

MVP / VIP

Pat Hartman

Super Moderator

Alhakeem1977

Registered User.

isladogs

MVP / VIP

Alhakeem1977

Registered User.

Similar threads

Users who are viewing this thread