Query with double link between tables (1 Viewer)

dlambert

Member
Local time
Today, 19:18
Joined
Apr 16, 2020
Messages
42
Hello,
I have a table called tblJobs that contains the IDs that are linked to other tables IDs.
Those ID numbers are meaningless to the users of the database, so i would like to make a query (or is there a better way to do this?) to 'clarify' the table of Jobs.
So for example if i add the fields
  • JobID from tblJobs
  • YachtName from tblYachts
  • Description from tblJobs
I get a list of all the JobIDs, and their corresponding YachtName (my clients), and the Job Description, this works as expected.

Here is my difficulty:
I would like to display the name of who created the job (CreatedByID) and who is the project Manager (ProjectManagerID), but if i select EmployeeName from tblEmployees, there are two links to tblJobs so it doesn't work....
Any advice on what i could do to resolve this problem?, is there a different approach i should take?

Thanks a lot for any assistance.
1596520492959.png
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:18
Joined
May 7, 2009
Messages
19,169
on your query design, drag tblJobs table.
then drag two (2) tblEmployees.

CreatedByID is joined to tblEmployee.EmployeeID
ProjectMangerID is joined to tblEmployee_1.EmployeeID
 

dlambert

Member
Local time
Today, 19:18
Joined
Apr 16, 2020
Messages
42
on your query design, drag tblJobs table.
then drag two (2) tblEmployees.

CreatedByID is joined to tblEmployee.EmployeeID
ProjectMangerID is joined to tblEmployee_1.EmployeeID
Thanks for your quick response, much appreciated, (i had already tried that but had something else wrong, after your message i investigated more and got it working).
On a similar topic, below is a screenshot of my overall relationships:
As you can see i already had 2 sets of tblEmployees to be able to do the double relationships to one table as i need. Is the way i have done it OK (or will it cause me problem in the future?). Basically i used tblEmployees_1 to do the second relationship to both tblJobs and tblWorkMaterials, and the original tblEmployees did all the other links.
1596524325984.png
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:18
Joined
May 7, 2009
Messages
19,169
you can drag As Many tblEmployees as there are related to a number of tables.
 

Users who are viewing this thread

Top Bottom