value in tbl A between the values of field 1 and field 2 in tbl B 2

[hovercraft]

Greetings,

I have 2 unrelated tables. Table1 has a filed called [my_value]. Table2 has three fields, [low_value] [high_value] and [cost].

I'm trying to select [cost] from Table2 where Table1.[my_value] is between [low_value] and [high_value] from Table2 and also select [my_value] from Table1

I'm lost since there isn't a related field.

Please help,
Hovercraft

 

[BigRed1212]

Try this.

Add a consecutively numbered field called joiner to each table.

Then try something like:

select c.thecost, c.thecheckvalue from

(SELECT a.cost as thecost, a.low_value as thelowvalue, a.high_value as thehighvalue, b.myvalue as thecheckvalue, a.joiner as howtojoin
FROM table2 AS a LEFT JOIN table1 AS b ON a.joiner = b.joiner
where b.myvalue < a.high_value) c

left join table2 d on c.howtojoin = d.joiner

where c.thecheckvalue > d.low_value

HTH.

 

[PHV]

SELECT T1.my_value, T2.cost
FROM Table1 AS T1, Table2 AS T2
WHERE T1.my_value Between T2.low_value And T2.high_value

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BigRed1212]

PHV,

I tried that when I was working on it but your query doesn't work for me in Access with the data I made up. I'll be interested to see what works for the orignal poster.

 

[hovercraft]

Thank you both for taking the time to post. Admittedly I did not attempt BigRed's suggestion. PHV's worked great. It was exactly what I needed.

I was struggling with using unrelated tables. When I would try to execute the query I originally had (my own code) I would receive a prompt for the 2nd table. I think this is due to me trying to use DLookup for the 2nd table.

Thanks for all the help given to me and others!

Hovercraft

 

[BigRed1212]

Cool.

I must have made up goofy data then. Learn every day.