Query from expression

  • Thread starter Thread starter LC
  • Start date Start date
L

LC

Hi,

Using the following tables,

Table1
=====
Product_CD Product_Desc
AAA Apple
PPP Pear

Table2
=====
Size_CD Size
01 Small
02 Medium
03 Large

Table 3
======
SKU Quantity
AAA01 10 ---> 10 Units of Apple Small
AAA03 5
PPP02 1

Is it possible to query table3 using expressions: Left(SKU,4) and
Right(SKU,2) to create a join query into Table1 and Table2? All inside
only one query? I know how to create the results in two steps, but is
it possible to use only one query?

Thanks
 
Correction

Is it possible to query table3 using expressions: Left(SKU,3) and
Right(SKU,2) to create a join query into Table1 and Table2? All
inside
only one query? I know how to create the results in two steps, but is
it possible to use only one query?
 
If you know how to do what you want in two steps, then I suggest you post the
two steps (two queries?).

With that information it will be easier to understand what you are attempting
to do and should be easy to construct a single query.

IF I understand your request correctly, you might be able to construct a query
like the following. Since the joins are not equi-joins (a=b) you can only
construct this query in SQL view.

SELECT Table3.SKU, Table3.Quantity, Table1.Product_Desc, Table2.Size
FROM (Table2 INNER JOIN Table3
ON Table3.Sku Like "*" & Table2.Size_CD)
INNER JOIN Table1
ON Table3.SKU Like Table1.ProductCD & "*"


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top