Left Join not working properly

[iTISTIC]

I have 3 tables I am trying to join in a query. They are as follows:

tblProducts
tblCustomerEquipment
tblEmployees

In SQL Server, this query would produce the proper result set:

SELECT tblProducts.name, tblCustomerEquipment.install_date,
tblEmployees.fullName
FROM tblProducts
LEFT JOIN tblCustomerEquipment ON tblProducts.product_id =
tblCustomerEquipment.product_id
LEFT JOIN tblEmployees ON tblCustomerEquipment.employee_id =
tblEmployees.employee_id

I have created the exact same query in the Access query designer, and
modified the joins so they are both one-to-many, but Access treats all
of the joins as INNER joins for some reason. In other words, the result
set only contains records from tblProducts that also exist in
tblCustomerEquipment that have records that exist in tblEmployees.

I am able to get the result set I want by using sub-queries for the
install_date and fullName fields but this will be bad performance wise.

Can anyone help me?

 

[John Welch]

Well, your SQL should work fine, except that you are missing some
parentheses. Try this:

SELECT tblProducts.name, tblCustomerEquipment.install_date,
tblEmployees.fullName
FROM (tblProducts
LEFT JOIN tblCustomerEquipment ON tblProducts.product_id =
tblCustomerEquipment.product_id)
LEFT JOIN tblEmployees ON tblCustomerEquipment.employee_id =
tblEmployees.employee_id;

When you say :

I have created the exact same query in the Access query designer, and
modified the joins so they are both one-to-many

I'm not sure if you really mean "one to many" or if you mean you set the
join properties to LEFT Joins. Having joins be "one to many" (the infinity
sign shows up on one side) means that referential integrity is being
inforced on a one to many relationship. It has nothing to do with whether
the join is INNER or LEFT. To make a left join, right click the join line in
the query designer, select Join Properties, and choose from the three
options (show all records from table blah and only those from table foo
where ....)

hope this helps
-John

 

[OfficeDev18 via AccessMonster.com]

What does Access' SQL look like? Please copy and paste here so we can analyze.


Thanks,

 

[iTISTIC]

Thanks for the help John. I have tried the query you suggested (pasted
below):

SELECT tblProductGenerics.generic, tblCustomerEquipment.install_date,
[tblEmployees].[last_name] & ", " & [tblEmployees].[first_name] AS
Installer, tblCustomerEquipment.account_number,
tblCustomerEquipment.comments
FROM tblProductGenerics (LEFT JOIN tblCustomerEquipment ON
tblProductGenerics.product_generic_id =
tblCustomerEquipment.product_generic_id) LEFT JOIN tblEmployees ON
tblCustomerEquipment.installed_by_employee_id =
tblEmployees.employee_id;

I receive the error "Syntax error in FROM clause" when I try to run the
query.

Basically all I am looking for is a result set that contains ALL of the
records in tblProductGenerics and only the records from
tblCustomerEquipment and tblEmployees when there is a related record in
tblCustomerEquipment. Not sure why this is so difficult in Access. In
MSSQL this is cake.

Thanks again for your time and help, everyone!

 

[Van T. Dinh]

Try (***untested***):

SELECT PG.generic, CE.install_date,
E.[last_name] & ", " & E.[first_name] AS Installer, CE.account_number,
CE.comments
FROM
(
tblProductGenerics AS PG LEFT JOIN
tblCustomerEquipment AS CE
ON PG.product_generic_id = CE.product_generic_id
) LEFT JOIN
tblEmployees AS E
ON CE.installed_by_employee_id = E.employee_id;

 

[John Welch]

Well, your parentheses are still in the wrong place. See my previous
example. If you build your query with the query builder, it will set it up
correctly for you.
-John

 

[iTISTIC]

John,

I apologize for the error on my part. When I place the parentheses in
the proper location the query runs without error, but still produces
the same results my original query produced -- it only shows records
from tblProductGenerics where there is a related record in
tblCustomerEquipment. I producted the original query in the designer
and had this same issue, which is why I posted this topic.

SELECT tblProductGenerics.generic, tblCustomerEquipment.install_date,
[tblEmployees].[last_name] & ", " & [tblEmployees].[first_name] AS
Installer, tblCustomerEquipment.account_number,
tblCustomerEquipment.comments
FROM (tblProductGenerics LEFT JOIN tblCustomerEquipment ON
tblProductGenerics.product_generic_id =
tblCustomerEquipment.product_generic_id) LEFT JOIN tblEmployees ON
tblCustomerEquipment.installed_by_employee_id =
tblEmployees.employee_id;

 

[iTISTIC]

Van,

This query executes, but the original problem still exists -- it only
shows records from tblProductGenerics where there is a related record
in tblCustomerEquipment. The same problem I am having with John's query
(even after correcting the parentheses mistake) and with the query
generated by the designer.

Thanks again, everyone, for your help. Anyone have any other
suggestions?

 

[Van T. Dinh]

Try the other way of joins, i.e.:

SELECT PG.generic, CE.install_date,
E.[last_name] & ", " & E.[first_name] AS Installer, CE.account_number,
CE.comments
FROM tblProductGenerics AS PG LEFT JOIN
(
tblCustomerEquipment AS CE LEFT JOIN
tblEmployees AS E
ON CE.installed_by_employee_id = E.employee_id
)
ON PG.product_generic_id = CE.product_generic_id

The join inside the parentheses may need to be changed to an INNER JOIN
rather than LEFT JOIN.

 

[iTISTIC]

Ok, Well I have decided to use the designer to start from scratch and
add tables one at a time to see what exactly is causing the problem. I
first ran the following query, which generated 15 records (the total
number of records in tblProductGenerics):

SELECT tblProductGenerics.generic
FROM tblProductGenerics;

I then added the tblCustomerEquipment table to the designer and added
the customer_equipment_id and account_number fields to the result set.
I also added a criteria for the account_number field as nominally I
only want this data for one account. They query generated is as
follows:

SELECT tblProductGenerics.generic,
tblCustomerEquipment.customer_equipment_id,
tblCustomerEquipment.account_number
FROM tblProductGenerics LEFT JOIN tblCustomerEquipment ON
tblProductGenerics.product_generic_id =
tblCustomerEquipment.product_generic_id
WHERE tblCustomerEquipment.account_number='1234';

This only returns ONE record since account_number '1234' only has ONE
record in tblCustomerEquipment. In MS-SQL I would simply move the where
clause to the LEFT JOIN statement, but this is not supported in Access.
How can I accomplish the same thing so that I always see all records in
tblProductGenerics, and only values for customer_equipment_id when a
related record exists in tblCustomerEquipment?

 

[iTISTIC]

Ok, I've played around and gotten the result set I want, but it's QUITE
convoluted and the performance of the query is horrible. It takes about
5-10 seconds to run, but the results are exactly what I need. There HAS
to be a better way!

PARAMETERS [@account_number] Value;

SELECT sq.generic, sq.customer_equipment_id,
tblCustomerEquipment.account_number, tblCustomerEquipment.comments,
[tblEmployees].[last_name] & ', ' & [tblEmployees].[first_name] AS
installed_by
FROM ([SELECT tblProductGenerics.generic, (SELECT customer_equipment_id
FROM tblCustomerEquipment WHERE product_generic_id =
tblProductGenerics.product_generic_id AND account_number =
@account_number) AS customer_equipment_id
FROM tblProductGenerics
]. AS sq LEFT JOIN tblCustomerEquipment ON sq.customer_equipment_id =
tblCustomerEquipment.customer_equipment_id) LEFT JOIN tblEmployees ON
tblCustomerEquipment.installed_by_employee_id =
tblEmployees.employee_id;

 

[John Spencer]

UNTESTED, but try the following modification

SELECT tblProductGenerics.generic,
tblCustomerEquipment.customer_equipment_id,
tblCustomerEquipment.account_number
FROM tblProductGenerics LEFT JOIN tblCustomerEquipment ON
tblProductGenerics.product_generic_id =
tblCustomerEquipment.product_generic_id
WHERE tblCustomerEquipment.account_number='1234' OR
tblCustomerEquipment.product_genericId is Null

 

[John Welch]

Shawn-
you left out the little detail of the WHERE clause in all your initial
posts. Were you testing it with this in there or not? If you use where to
limit what you get from tblCustomerEquipment, then of course you won't get
records that have nothing in that table. John Spencer's solution will take
care of this.
-John

 

[iTISTIC]

John,

That would work perfectly in MSSQL, and I had actually already tried
that, but it only returns ONE record in Access, in essense it's the
same as an INNER JOIN.

I am so baffled by the difficulty of this small simple task. There is
no way others have not run into this issue before?

Shawn

 

[John Spencer]

That is strange, this type of query has always worked for me. Unless, of
course, my memory is failing with age.

I just tested with a couple of my tables and in worked for me.

What version of Access? Where is the data - Jet, MSDE, or MS SQL Server?

Access project (.adb)or Access database (.mdb)?

If you are hooked to an SQL server are you using ODBC to connect or other
method?

 

[iTISTIC]

Always worked for me as well, but in MSSQL. Haven't done an Access DB
for a long time until this one.

DB is in Access 2003 format and is an MDB file. Front end is in one MDB
file, data resides in another MDB file.

 

[iTISTIC]

... Anyone have any idea?

 

[John Spencer]

This thread is so buried that it might be a good idea to start a new one. I
have nothing else to suggest.
Sorry.