A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Lookup values from another table



 
 
Thread Tools Display Modes
  #1  
Old February 21st, 2007, 03:42 AM posted to microsoft.public.access.queries
nubee
external usenet poster
 
Posts: 1
Default 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  
Old February 21st, 2007, 06:06 AM posted to microsoft.public.access.queries
Van T. Dinh
external usenet poster
 
Posts: 1,048
Default 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  
Old February 21st, 2007, 06:16 AM posted to microsoft.public.access.queries
Marshall Barton
external usenet poster
 
Posts: 5,361
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:59 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.