Left Join?

[Opal]

I am trying to create a query
that will show me all the records in
my qryZoneProcess even if there
is no match in my EMPInfo table.

I have the following SQL:

SELECT DISTINCTROW EMPInfo.SUPLink,
qryZoneProcess.ZoneProcessID,
qryZoneProcess.Process, qryZoneProcess.TLGroupID,
EMPInfo.LastName
FROM EMPInfo LEFT JOIN qryZoneProcess
ON EMPInfo.ProcessID = qryZoneProcess.ZoneProcessID
WHERE (((EMPInfo.SUPLink)=
[Forms]![HoldingInfo]![txtHoldSUPNumber])
AND ((qryZoneProcess.TLGroupID)=2));

To get my desired results the query, above,
should produce 6 records, 3 with Employee names
and 3 without. I only get 3 with Employee names.

What am I missing?

 

[Sylvain Lafontaine]

It's the AND ((qryZoneProcess.TLGroupID)=2)) who is giving you a problem:

SELECT DISTINCTROW EMPInfo.SUPLink,
qryZoneProcess.ZoneProcessID,
qryZoneProcess.Process, qryZoneProcess.TLGroupID,
EMPInfo.LastName
FROM EMPInfo LEFT JOIN qryZoneProcess
ON EMPInfo.ProcessID = qryZoneProcess.ZoneProcessID
WHERE (((EMPInfo.SUPLink)=
[Forms]![HoldingInfo]![txtHoldSUPNumber])
AND (qryZoneProcess.TLGroupID is Null OR (qryZoneProcess.TLGroupID)=2));

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Sylvain Lafontaine]

Or you have mixed EMPINfo and qryZoneProcess in your Left Join:

SELECT DISTINCTROW EMPInfo.SUPLink,
qryZoneProcess.ZoneProcessID,
qryZoneProcess.Process, qryZoneProcess.TLGroupID,
EMPInfo.LastName
FROM qryZoneProcess LEFT JOIN EMPInfo
ON EMPInfo.ProcessID = qryZoneProcess.ZoneProcessID
WHERE ((EMPInfo.SUPLink is Null OR (EMPInfo.SUPLink)=
[Forms]![HoldingInfo]![txtHoldSUPNumber])
AND ((qryZoneProcess.TLGroupID)=2));

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Opal]

Thanks Sylvain,

Your first suggestion still gives me the same
3 results.....

The second suggestion gives me all the names for
the SUPLink, but I need all the Processes from
the qryZoneProcess.....

hmmmmmm

 

[Sylvain Lafontaine]

Then you have to provide an example of data that you have in the tables and
of the result that you want to get if you want to get some further
explanations. At this moment, I don't even know from what table you want
all the records.

Second, to make it clearer: when you have a Left Join, you must be careful
about any testing (WHERE ...) that you'll put on the other side because all
the columns on this second side will be (or might be) null for many rows.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Opal]

Thank you for your continued support.

My EMPInfo table is the information on
my employees. It presently contains:

EMPNo / EMPName / SUPLink / AreaID

My ZoneProcess table contains:

ZoneProcessID / ProcessName / AreaID / TLGroupID

We run on two shifts but the same processes
occur in the each shift in the same Areas. The
SUPLink separates the employees by their supervisor
ID and also indicates their shift. 3 - 5 employees
report to a Team Lead (TL) and 3 - 5 Team Leads
report to a Supervisor. I am trying to create a
form for the supervisors to manage their organization
that shows each Team Lead and each employee
under the Team lead and what process they work
on. There are approximately 400 + employees
over both shifts.

The AreaID field is a common # for both shifts. So
for the Supervisor in group 1 on A shift and the
Supervisor in group 1 on B shift, the areaID is the
same but the SUPLInk is different.

The form should only allow the supervisor to edit
the Employee names, not the process names
as they are fixed......

Does this make sense? Will it help you guide me
in the right direction?

 

[Sylvain Lafontaine]

It helps a little more but not that much. For example, AreaID is not even
in the example of query that you have posted in your original post (OP).

However, if I read again your OP a few times, I notice the following
sentence: "To get my desired results the query, above, should produce 6
records, 3 with Employee names and 3 without. I only get 3 with Employee
names."; so I'm guessing from that you want to have all the records in the
qryZoneProcess with TLGroupID = 2 and those associated employees but only if
those employees belongs to a certain supervisor.

For this, the EMPInfo table must be on the right side of the Left Join and -
very important - the filtering for EMPInfo.SUPLink must be done *before* the
Left Join and not after. To do this, you must create a View/Query on
EMPInfo that will filter for txtHoldSUPNumber and use this in your Left
Join:

SELECT DISTINCTROW EMPInfoView.SUPLink,
qryZoneProcess.ZoneProcessID,
qryZoneProcess.Process, qryZoneProcess.TLGroupID,
EMPInfoView.LastName
FROM qryZoneProcess LEFT JOIN EMPInfoView
ON EMPInfoView.ProcessID = qryZoneProcess.ZoneProcessID
WHERE qryZoneProcess.TLGroupID=2;


And for EMPInfoView query:

Select * from EMPInfo WHERE
EMPInfo.SUPLink=[Forms]![HoldingInfo]![txtHoldSUPNumber]


You could also move the condition directly on the JOIN but I think that
Access don't really like this syntaxe:

SELECT DISTINCTROW EMPInfo.SUPLink,
qryZoneProcess.ZoneProcessID,
qryZoneProcess.Process, qryZoneProcess.TLGroupID,
EMPInfo.LastName
FROM qryZoneProcess LEFT JOIN EMPInfo
ON (EMPInfo.ProcessID = qryZoneProcess.ZoneProcessID AND EMPInfo.SUPLink=
[Forms]![HoldingInfo]![txtHoldSUPNumber])
WHERE qryZoneProcess.TLGroupID=2;

An easier solution for Access/JET would be use a subquery instead to
prefilter the EMPInfo table:


SELECT DISTINCTROW EMPInfoView.SUPLink,
qryZoneProcess.ZoneProcessID,
qryZoneProcess.Process, qryZoneProcess.TLGroupID,
EMPInfoView.LastName
FROM qryZoneProcess LEFT JOIN (Select * from EMPInfo Where EMPInfo.SUPLink=
[Forms]![HoldingInfo]![txtHoldSUPNumber]) as EMPInfoView
ON EMPInfoView.ProcessID = qryZoneProcess.ZoneProcessID
WHERE qryZoneProcess.TLGroupID=2;

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Opal]

Thank you Sylvain.....

I think I need to approach this from
a different angle..... I have been looking
and looking and since the user / supervisor
needs to input the process for each employee
as well as indicate each Team Lead ID, I
need to re-work how I am approaching this.

Thank you for your insight.

 

[Opal]

Your sub-query produces the desired results...
but I cannot edit the form on which the query
populates.....so.....I need to look elsewhere

 

[Sylvain Lafontaine]

At least, know you know how does these Left Join work.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)