Intermediate Query

[wizzz_wizzz]

Hi All,



I inherited a DB whereby an intermediate query (QueryA) was created to Select some fields and create a new column using expression - named "PN" to extract part of a field: PN: (Left([A_Table1].[Part Number],3)) & (Mid([A_Table1].[Part Number],4,5)).

The purpose is to make use of this intermediate query (and using PN) to form another query (QueryB) with another table.

May I know is such an intermediate query a normal practice? Are there any alternative/better ways of doing it?

[xps35]

This can be in one query, but not in design view. You need to do it in SQL view.

SELECT A_Table1.[Part Number] FROM A_Table1 INNER JOIN B_Table2 ON Left(A_Table1.[Part Number], 8) = B_Table2.[Part Number]

Notice there is no need for left and mid function. Only one left will do.

[wizzz_wizzz]

This can be in one query, but not in design view. You need to do it in SQL view.

SELECT A_Table1.[Part Number] FROM A_Table1 INNER JOIN B_Table2 ON Left(A_Table1.[Part Number], 8) = B_Table2.[Part Number]

Notice there is no need for left and mid function. Only one left will do.

Hi, thank you for you advice. The Left and Mid function was used because the 2 Tables to be joined are as below:

[CJ_London]

The Left and Mid function was used because the 2 Tables to be joined are as below:

in that case your query A does not return what you believe it returns - a simple opening of the query would have shown that

left 3 returns '071' - fine
mid 4,5 returns '-3106' - not what you think


So assuming your queryA is wrong and it should be mid 5,5 you can stlll apply to xps's suggestion

SELECT A_Table1.[Part Number] FROM A_Table1 INNER JOIN B_Table2 
ON Left(A_Table1.[Part Number], 3) &  Mid(A_Table1.[Part Number], 5,5)= B_Table2.[Part Number]

or you can use a cartesian query

SELECT A_Table1.[Part Number] FROM A_Table1, B_Table2
WHERE Left(A_Table1.[Part Number], 3) & Mid(A_Table1.[Part Number], 5,5)= B_Table2.[Part Number]

To answer your question, nothing wrong with using an intermediate query, all 3 methods lose the benefit of indexing so will be slow for large volumes of data. Personally I try to avoid using them because of the lack of indexing but they are simple to apply. It all depends on the overall requirement.

To use indexing I might create an additional indexed column in table1 and populate it with the calculated value. Or I might create an intermediate table to link the two values. Or I might just index the field in table 1 and use a query like

SELECT A_Table1.[Part Number] FROM A_Table1, B_Table2
WHERE A_Table1.[Part Number] Like left(Table2.[Part Number],3) & "*" and Mid(A_Table1.[Part Number], 5,5)= mid(B_Table2.[Part Number],4)

not using an initial * with the Like means indexing can be used so the comparison on the 5 digits is on a much smaller non indexed recordset. As above, depends on your data. Your example shows all starting with 071, if that applies to all the data then not much point in this method