Left Join not working properly

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;

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

"iTISTIC" wrote in message
oups.com...
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?

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" wrote in message
ups.com...
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;

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

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" wrote in message
oups.com...
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

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.

... Anyone have any idea?

This thread is so buried that it might be a good idea to start a new one. I
have nothing else to suggest.
Sorry.
"iTISTIC" wrote in message
ups.com...
.. Anyone have any idea?