Query problem

[sinyce]

I have two tables

Table 1
SALES LNUM AMOUNT
ABC 101 100
ABC 102 200
ABC 103 450
XYZ 674 234

Table2
SALES LIMIT
ABC 4000
XYZ 2000

Trying to write a Query to give me this result:

SALES LNUM AMOUNT LIMIT
ABC 101 100 4000
ABC 102 200
ABC 103 450
XYZ 674 234 2000

Help! This is driving me crazy

 

[JonFer]

Write a query (name it "MinLNUM") that gets the lowest LNUM value for each SALES value:

Select SALES, Min(LNUM) as LNUM
From Table1
Group By SALES

Create a query joining your two tables on SALES. Instead of pulling LIMIT directly from Table2, add the MinLNUM query to the query design and join it on SALES to Table1. Create a new field:

LIMIT: IIf(MinLNUM.LNUM=Table1.LNUM,Table2.LIMIT,Null)

Be sure to sort by Table1.SALES and Table1.LNUM

 

[lespaul]

Does this work:

SELECT T1.SALES, LNUM, AMOUNT, LIMIT
FROM TABLE1 T1
LEFT JOIN TABLE2 T2 ON T1.SALES = T2.SALES

Leslie