Query Design multiple employees in one table, showing ID not names (1 Viewer)

Chief

Registered User.
Local time
Today, 01:40
Joined
Feb 22, 2012
Messages
156
Hello,

I have an EmployeeT and a JobInfoT.
The JobInfoT is holding the ID number on multiple employees and is showing their ID instead of the Employee name.
If I relate one of the ID job JobInfoT to the EmployeeT then I can get the correct result in my form.
However If I have more than one Relationship to the EmployeeT I get either duplicates or wrong information.

The 2 fields in question are the "ProjectManager" and "SetOut_Designer", but I will most likely being adding more Employees.

I mainly use Query Design as I am not familiar with SQL.
I know I should be able to use - FirstName & " " & LastName from the EmployeeT but not working for me.
I currently just have the fields from the JobInfoT so I get the correct ID.

Screen Shots attached.

Thank you in advance

Form Fields
1634521460756.png


Form Property Sheet and Query Name
1634521507773.png


Query Design
1634521610248.png


1634521775297.png
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:40
Joined
May 7, 2009
Messages
19,169
on your Query add 2 Columns:

ProjMgrName: [EmployeeT].[FirstName] & " " & [EmployeeT].[LastName]

DetailerName: [EmployeeT_1].[FirstName] & " " & [EmployeeT_1].[LastName]


output this two Calculated columns to your form (in "replacement" of what you have right now).
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:40
Joined
May 7, 2009
Messages
19,169
one Comment though to your Query.
you are using Inner Join and might render your Query "non-updateable" if either on
your Joining fields does not exists.
 

Chief

Registered User.
Local time
Today, 01:40
Joined
Feb 22, 2012
Messages
156
one Comment though to your Query.
you are using Inner Join and might render your Query "non-updateable" if either on
your Joining fields does not exists.
Thanks for the additional information.

What/How should I change this?

thanks
 

Chief

Registered User.
Local time
Today, 01:40
Joined
Feb 22, 2012
Messages
156
on your Query add 2 Columns:

ProjMgrName: [EmployeeT].[FirstName] & " " & [EmployeeT].[LastName]

DetailerName: [EmployeeT_1].[FirstName] & " " & [EmployeeT_1].[LastName]


output this two Calculated columns to your form (in "replacement" of what you have right now).
Thank you, Looks like that is working.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:40
Joined
Feb 19, 2002
Messages
42,970
Using inner rather than outer joins won't impact whether or not the query is updateable but it willaffect what records are shown. An inner join, which is the default join type pulls together data from tbB when it matches a value in tblA. However if the field in tblA is null, that will never match a record in tblB and so you will not pull in the record from tblA. To avoid this, you use an outer join. In this case a LEFT outer join. That tells the database engine that you want the row from tblA even when there is no matching row in tblB.

Most foreign keys are required and so inner joins will always work. However, when the foreign key is optional, then you will need to use a left join to bet the records you want.

To change the join tpe, double click on the join line and choose from the two options. Read them carefully.

I've attached a picture of the Venn diagrams that show what data is selected by which join type. The pictures are left to right in the order of the list. Note that Jet/ACE have no direct way to implement a full outer join. That is done using a left join query

SQLJoinTypes.JPG
 
Last edited:

Chief

Registered User.
Local time
Today, 01:40
Joined
Feb 22, 2012
Messages
156
Using inner rather than outer joins won't impact whether or not the query is updateable but it willaffect what records are shown. An inner join, which is the default join type pulls together data from tbB when it matches a value in tblA. However if the field in tblA is null, that will never match a record in tblB and so you will not pull in the record from tblA. To avoid this, you use an outer join. In this case a LEFT outer join. That tells the database engine that you want the row from tblA even when there is no matching row in tblB.

Most foreign keys are required and so inner joins will always work. However, when the foreign key is optional, then you will need to use a left join to bet the records you want.

To change the join tpe, double click on the join line and choose from the two options. Read them carefully.

I've attached a picture of the Venn diagrams that show what data is selected by which join type. The pictures are left to right in the order of the list.

View attachment 95420
Awesome mate,
Thank you, very informative.
 

Users who are viewing this thread

Top Bottom