Access Query Running Forever Help???

[Mac1206]

This query runs forever, can this be written better to in order to get a faster result, see below...

SELECT Calculation_Strategy_Price_Tbl.Product_Family, Calculation_Strategy_Price_Tbl.Product_SubFamily, Calculation_Strategy_Price_Tbl.Shape, Calculation_Strategy_Price_Tbl.Size, Calculation_Strategy_Price_Tbl.Ship_to_City, Calculation_Strategy_Price_Tbl.Ship_to_State, Min(Freight_GNAPS_Norm_Transactions_Tbl.Rate) AS MinOfRate
FROM Freight_GNAPS_Norm_Transactions_Tbl INNER JOIN ((List_Price_GNAPS_Tbl INNER JOIN Xref_Shape_Tbl ON List_Price_GNAPS_Tbl.Shape = Xref_Shape_Tbl.GNAPS_SHAPE_ID) INNER JOIN Calculation_Strategy_Price_Tbl ON (Xref_Shape_Tbl.Shape = Calculation_Strategy_Price_Tbl.Shape) AND (List_Price_GNAPS_Tbl.Size = Calculation_Strategy_Price_Tbl.Size)) ON (Freight_GNAPS_Norm_Transactions_Tbl.Ship_to_State = Calculation_Strategy_Price_Tbl.Ship_to_State) AND (Freight_GNAPS_Norm_Transactions_Tbl.City_Dest = Calculation_Strategy_Price_Tbl.Ship_to_City) AND (Freight_GNAPS_Norm_Transactions_Tbl.GNAPS_List_Price = List_Price_GNAPS_Tbl.Plant)
GROUP BY Calculation_Strategy_Price_Tbl.Product_Family, Calculation_Strategy_Price_Tbl.Product_SubFamily, Calculation_Strategy_Price_Tbl.Shape, Calculation_Strategy_Price_Tbl.Size, Calculation_Strategy_Price_Tbl.Ship_to_City, Calculation_Strategy_Price_Tbl.Ship_to_State
HAVING (((Calculation_Strategy_Price_Tbl.Product_Family)="BEAMS") AND ((Calculation_Strategy_Price_Tbl.Product_SubFamily)="WIDE FLANGE BEAMS"));

 

[Joe4]

This is very difficult to answer without knowing the structure of your database (including tables and relationships), what your data looks like, and what it is you are trying to accomplish.

Since we do not have any of that information, all I can offer is this. Make sure that all the fields you are joining on are Indexed, and that every table has a Primary Key.

 

[Mac1206]

This is very difficult to answer without knowing the structure of your database (including tables and relationships), what your data looks like, and what it is you are trying to accomplish.

Since we do not have any of that information, all I can offer is this. Make sure that all the fields you are joining on are Indexed, and that every table has a Primary Key.

In viewing this query, could you give me an example of one of the fields to index on Joe...Thanks

 

[Joe4]

All the fields listed in your JOIN clauses...

INNER JOIN ((List_Price_GNAPS_Tbl INNER JOIN Xref_Shape_Tbl ON List_Price_GNAPS_Tbl.Shape = Xref_Shape_Tbl.GNAPS_SHAPE_ID) INNER JOIN Calculation_Strategy_Price_Tbl ON (Xref_Shape_Tbl.Shape = Calculation_Strategy_Price_Tbl.Shape) AND (List_Price_GNAPS_Tbl.Size = Calculation_Strategy_Price_Tbl.Size)) ON (Freight_GNAPS_Norm_Transactions_Tbl.Ship_to_State = Calculation_Strategy_Price_Tbl.Ship_to_State) AND (Freight_GNAPS_Norm_Transactions_Tbl.City_Dest = Calculation_Strategy_Price_Tbl.Ship_to_City) AND (Freight_GNAPS_Norm_Transactions_Tbl.GNAPS_List_Price = List_Price_GNAPS_Tbl.Plant)

 

[Mac1206]

All the fields listed in your JOIN clauses...

Thanks Joe, will do...

 

[Joe4]

Here are some other tips that may help: Microsoft Access tips: Optimizing queries

 

[Mac1206]

Here are some other tips that may help: Microsoft Access tips: Optimizing queries

Thanks again Joe, those tips are very helpful indeed from Allen Browne...Allen Browne's Browne's