If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Lookup values from another table
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... |
#2
|
|||
|
|||
Lookup values from another table
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 ) ======== -- HTH Van T. Dinh MVP (Access) "nubee" u31915@uwe wrote in message news:6e1dc2ef99282@uwe... 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... |
#3
|
|||
|
|||
Lookup values from another table
nubee wrote:
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 -- Marsh MVP [MS Access] |
Thread Tools | |
Display Modes | |
|
|