How to join two tables with relationship (1 Viewer)

Alhakeem1977

Registered User.
Local time
Today, 19:32
Joined
Jun 24, 2017
Messages
308
How can I join tables as below:?
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
tblFacilityRegister
""""""""""""""""""""""""""
ID (PK)
SenderID (FK)
ReceiverID (PK)
ScannedBy (FK)
,,,,,,,,,,,,,,,,,,,,
tblUsers
""""""""""""
UserID (PK)
FirstName
LastName
EmailID

I want to join UserID (PK) from tblUsers to all (FK)'s from tblFacilityRegister, how can I do that? I did it in the relationship window by duplicating the tblUsers twice, it works fine but when I want to place the SenderID, ReceiverID and ScannedBy Firstnames and LastNames in a query I got an empty values.
Is the there any way solve this issue?
Do I have restructure the tables?

Your earliest response is highly appreciated.
Thanks in advance
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 16:32
Joined
Jan 14, 2017
Messages
18,186
Try using an outer join from tblFacilityRegister to 3 copies of tblUsers linking one of the FK fields each time
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:32
Joined
May 7, 2009
Messages
19,169
what you need is a Union query to show
those info:

SELECT tblFacilityRegister.ID As FacilityID, "Sender" As Role, tblUsers.UserID, tblUsers.FirstName, tblUsers.LastName, tblUsers.Email
FROM tblFacilityRegister INNER JOIN tblUsers ON tblFacilityRegister.SenderID = tblUsers.UserID

UNION ALL
SELECT tblFacilityRegister.ID As FacilityID,"Receiver" As Role, tblUsers.UserID, tblUsers.FirstName, tblUsers.LastName, tblUsers.Email
FROM tblFacilityRegister INNER JOIN tblUsers ON tblFacilityRegister.ReceiverID = tblUsers.UserID

UNION ALL
SELECT tblFacilityRegister.ID As FacilityID,"ScannedBy" As Role, tblUsers.UserID, tblUsers.FirstName, tblUsers.LastName, tblUsers.Email
FROM tblFacilityRegister INNER JOIN tblUsers ON tblFacilityRegister.ScannedBy = tblUsers.UserID


unfortunately it won't be updatable.
 

Alhakeem1977

Registered User.
Local time
Today, 19:32
Joined
Jun 24, 2017
Messages
308
Thanks Ridders for your kind reply, I tried that but I didn't get the values in the query as I mentioned earlier
Try using an outer join from tblUsers to 3 copies of the other table linking to one of the FK fields each time
 

isladogs

MVP / VIP
Local time
Today, 16:32
Joined
Jan 14, 2017
Messages
18,186
Sorry - it should have been 3 copies of tblUsers! I've corrected my original post

what you need is a Union query to show
those info:
...
unfortunately it won't be updatable.

Which is why I didn't suggest that method! :)

Using 3 copies of the users table means the query is updatable.
It also works with INNER JOINS (as long as all names are in the User table)

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;

See attached DB
 

Attachments

  • JoinTablesEx.accdb
    492 KB · Views: 93
Last edited:

Alhakeem1977

Registered User.
Local time
Today, 19:32
Joined
Jun 24, 2017
Messages
308
unfortunately it won't be updatable.
Thanks for your kind reply, I want it to be updatable it's a continuous search form.

How can I restructure the relationship between these tables, shall I create a junction table to do that? If yes, how can I do that?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:32
Joined
May 7, 2009
Messages
19,169
Its a no no ridder. If all the roles are performed by one person, it will show the record. If different people BLank row?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:32
Joined
May 7, 2009
Messages
19,169
if you are just going to show and update in
continuous form, the form's record source:

SELECT * FROM tblFacilityRegister;

put all the your field in the form, SenderID,
ReceiverID, ScannedBy.


1. right click each SenderID textbox, select
2. Change To->combobox:

3. go to the textbox's Property->Data->Row Source:

4. SELECT tblusers.UserID, [FirstName] & " " & [LastName] As Names FROM tblUsers;

5. now still in Property window. Select Format Tab.
Column Count: 2
Column Widths: 0; 1
List Width: 1

''''
6. do this steps for the remaining 2 textbox
 

isladogs

MVP / VIP
Local time
Today, 16:32
Joined
Jan 14, 2017
Messages
18,186
Its a no no ridder. If all the roles are performed by one person, it will show the record. If different people BLank row?

Wrong. It's a yes. Look at the example I posted

P.S. Congrats on reaching 5000 posts
 
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 12:32
Joined
Jan 23, 2006
Messages
15,364
Alhakeem1977,

Please describe the business in simple terms so we understand the issue in context. I don't think your whole setup and requirement is just limited to the 2 tables you provided.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:32
Joined
May 7, 2009
Messages
19,169
You are cirrect youare truly a genious?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:32
Joined
Feb 28, 2001
Messages
26,999
First, a clarification because this must be a typo - but we have to be sure of your intent.

,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
tblFacilityRegister
""""""""""""""""""""""""""
ID (PK)
SenderID (FK)
ReceiverID (PK) <<< Surely you meant (FK)?
ScannedBy (FK)
,,,,,,,,,,,,,,,,,,,,
tblUsers
""""""""""""
UserID (PK)
FirstName
LastName
EmailID

Second: How to mechanically establish the relationship.

In the Relationships window, you can add a table more than once. It does not make a separate copy of the table, it just makes a copy of the pointer. So you would see Users, Users(2), and Users(3) if you added Users to the Relationships window three times.

Now you make one relationship from SenderID to users, another with the ReceivedID to Users(2), and a third with the ScanedByID to Users(3). This makes the relationships stay orderly and you don't need a UNION to establish the relationships.

HOWEVER, the nature of these relationships is EXTREMELY important. Can you guarantee that EVERY USER who could appear as a Sender, Receiver, or Scanner will be in the Users table? It would be tempting to make the relationships into a 1/1 category, but that would be wrong. If you did, you would not be able to add information except through a query that expressed the JOIN. Make the relationship 1/many, perhaps with the option for each field to be the "one" side and each corresponding entry on the user side to be "many". I.e. "all SenderIDs in tblFacilityRegister and any matching entries from tblUsers."

Doing it this way means you could add records to Users for those users who haven't sent, received, or scanned anything YET (new employees?). Also, doing it this way would allow you to create a form to populate those fields by having a combo box driven by your tblUsers as a .Rowsource. The combo box wizard could handle this one trivially if the relationship was set as I described. Ditto for the other two fields.

Doing this any other way? Well, nothing is totally impossible, but Access won't cooperate quite so well if you don't do this more or less this way. I'm not saying my way is perfect, just that it is more "orthodox" with respect to the way Access works.
 

isladogs

MVP / VIP
Local time
Today, 16:32
Joined
Jan 14, 2017
Messages
18,186
I'm no more a genius than DJT... But perhaps more stable. :D
 

Alhakeem1977

Registered User.
Local time
Today, 19:32
Joined
Jun 24, 2017
Messages
308
Thanks all for your replies.

The_Doc_Man
ReceiverID (PK) <<< Surely you meant (FK)?
Sorry, ReceiverID is FK and I didn't mention all FK's are Numbers not texts type.
My aim is to restructure the relationship between the tables.

I am sorry all if didn't convey exactly my need.
 

isladogs

MVP / VIP
Local time
Today, 16:32
Joined
Jan 14, 2017
Messages
18,186
@alkaheem
Did you see my working solution in post 5?

It will work just as well with number datatypes for your key fields
By all means restructure your tables if it improves your database ....
but as the example shows, you don't need to do so to get this part working
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:32
Joined
May 7, 2009
Messages
19,169
Why? Ok, get your refund from the counter
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:32
Joined
May 7, 2009
Messages
19,169
Its for the op, at least give him something back.
 

Alhakeem1977

Registered User.
Local time
Today, 19:32
Joined
Jun 24, 2017
Messages
308
Did you see my working solution in post 5?
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.
 

Users who are viewing this thread

Top Bottom