Query from expression

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
 
L

LC

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?
 
J

John Spencer

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top