Prev: Query result shows all fields
Next: Multiple criteria IIF function help
From: jenniferspnc on 17 May 2010 11:43
I'm trying to rewrite queries for better performance and am stuck on one.
This is how it is written now:
SELECT tbl_OrderParts.UniqueID, tbl_OrderParts.Part_Number,
tbl_OrderParts.Sales_Order, tbl_parts.Product_Description, tbl_parts.ECCN,
tbl_parts.Manufacturer_ID, tbl_manufacturer.Manufacturer
FROM tbl_manufacturer INNER JOIN (tbl_parts INNER JOIN tbl_OrderParts ON
tbl_parts.Part_Number = tbl_OrderParts.Part_Number) ON
tbl_manufacturer.Manufacturer_ID = tbl_parts.Manufacturer_ID;

I rewrote it as follows but now it's not updatable (does not allow entry in
my form):
SELECT tbl_OrderParts.UniqueID, tbl_OrderParts.Part_Number,
tbl_OrderParts.Sales_Order, tbl_parts.Product_Description, tbl_parts.ECCN,
tbl_parts.Manufacturer_ID, tbl_manufacturer.Manufacturer
FROM tbl_manufacturer, tbl_parts, tbl_OrderParts
WHERE (((tbl_parts.Part_Number)=[tbl_OrderParts].[Part_Number]) AND
((tbl_manufacturer.Manufacturer_ID)=[tbl_parts].[Manufacturer_ID]));

Where have I gone wrong? The first query is super slow and thought I should
revisit using the Inner Joins...

From: Marshall Barton on 17 May 2010 13:05
jenniferspnc wrote:

>I'm trying to rewrite queries for better performance and am stuck on one.
>This is how it is written now:
>SELECT tbl_OrderParts.UniqueID, tbl_OrderParts.Part_Number,
>tbl_OrderParts.Sales_Order, tbl_parts.Product_Description, tbl_parts.ECCN,
>tbl_parts.Manufacturer_ID, tbl_manufacturer.Manufacturer
>FROM tbl_manufacturer INNER JOIN (tbl_parts INNER JOIN tbl_OrderParts ON
>tbl_parts.Part_Number = tbl_OrderParts.Part_Number) ON
>tbl_manufacturer.Manufacturer_ID = tbl_parts.Manufacturer_ID;
>
>I rewrote it as follows but now it's not updatable (does not allow entry in
>my form):
>SELECT tbl_OrderParts.UniqueID, tbl_OrderParts.Part_Number,
>tbl_OrderParts.Sales_Order, tbl_parts.Product_Description, tbl_parts.ECCN,
>tbl_parts.Manufacturer_ID, tbl_manufacturer.Manufacturer
>FROM tbl_manufacturer, tbl_parts, tbl_OrderParts
>WHERE (((tbl_parts.Part_Number)=[tbl_OrderParts].[Part_Number]) AND
>((tbl_manufacturer.Manufacturer_ID)=[tbl_parts].[Manufacturer_ID]));
>
>Where have I gone wrong? The first query is super slow and thought I should
>revisit using the Inner Joins...


While the two queries are logically the same, there may be
something about the second one that Access thinks is too
complex to be updatable. Or, maybe you are trying to update
a field in one of the dependent tables that is not updatable
in either query.

IMO, the Inner Join query is the preferred approach. If it
is slow, the first thing to do is to make sure that you have
an index in each table for the field used in the ON and
WHERE expressions. It looks like some of the fields should
be primary keys and that should be sufficient if you have
properly created the relationships between the tables.

--
Marsh
MVP [MS Access]
 | 
Pages: 1
Prev: Query result shows all fields
Next: Multiple criteria IIF function help