Lookup values from another table

[nubee]

Hi!

I want to create a query that will lookup the value from another table.

my first table contains the amount(field) and the other table contains the
minimum bracket of the amount(1st field) and the value(2nd field).

for example i have an amount of 3,500 from my first table and 3000 is the
minimum bracket with 50 value and the next data is 4000 with 60 value from
the 2nd table. It means 3500 is between two data. How can i get the 50 value.


thanks...

 

[Van T. Dinh]

Try something like:

========
SELECT T1.AmountField, T2.ValueField
FROM FROM [Table1] AS T1, [Table2] AS T2
WHERE T2.BracketField =
(
SELECT Max(T2_Copy2.BracketField)
FROM [Table2] AS T2_Copy2
WHERE T2_Copy2.BracketField < T1.AmountField
)
========

 

[Marshall Barton]

I want to create a query that will lookup the value from another table.

my first table contains the amount(field) and the other table contains the
minimum bracket of the amount(1st field) and the value(2nd field).

for example i have an amount of 3,500 from my first table and 3000 is the
minimum bracket with 50 value and the next data is 4000 with 60 value from
the 2nd table. It means 3500 is between two data. How can i get the 50 value.

SELECT table1.amount,
(SELECT TOP 1 table2.[value]
FROM table2
WHERE table2.minbracket <= table1.amount
ORDER BY table2.minbracket DESC) As BracketValue
FROM table1