Field as a lookup from a 2nd table

  • Thread starter Thread starter B Nieds
  • Start date Start date
B

B Nieds

Hi:

Is it possible in a query to set a field to lookup a value from a range of
values in a second table?

I have two tables. Table1 has a field called CODE. Table2 has three fields
one for GROUP, one for BEGIN, and one for END. What I would like to do is
display fields from Table1 including CODE and the GROUP field from Table2
where CODE from Table1 is between BEGIN and END.

All fields are numbers stored in text fields 6 characters in length. All
codes will exists between one of the BEGIN-END pairs. There are 20 records
in Table2 to define all the possible BEGIN-END pairs each with a unique
GROUP character.

Thanks
Bill
 
If the resultant query can be read-only, use a subquery. Type something this
into the Field row of your query:

WhatGroup: (SELECT TOP 1 Table2.Group FROM Table2 WHERE Table1.Code Between
Table2.Begin And Table2.End ORDER BY Table2.Group )

If the results need to be updatable, you could use DLookup(), but expect the
performance of a snail:
DLookup("GROUP", "Table2",
Code: 
 & " Between [BEGIN] And [END]")

There is a replacement for DLookup() that runs about twice as fast, but
that's still slow. See:
http://allenbrowne.com/ser-42.html

BTW, the word "Group" has special meaning in SQL, so it might be good to
avoid that as a field name.
 
Hi Allen:

Thanks for the reply. This is just what I needed.

Thanks
Bill

Allen Browne said:
If the resultant query can be read-only, use a subquery. Type something this
into the Field row of your query:

WhatGroup: (SELECT TOP 1 Table2.Group FROM Table2 WHERE Table1.Code Between
Table2.Begin And Table2.End ORDER BY Table2.Group )

If the results need to be updatable, you could use DLookup(), but expect the
performance of a snail:
DLookup("GROUP", "Table2",
Code: 
 & " Between [BEGIN] And [END]")

There is a replacement for DLookup() that runs about twice as fast, but
that's still slow. See:
http://allenbrowne.com/ser-42.html

BTW, the word "Group" has special meaning in SQL, so it might be good to
avoid that as a field name.

--
Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

[QUOTE="B Nieds"]
Is it possible in a query to set a field to lookup a value from a range of
values in a second table?

I have two tables. Table1 has a field called CODE. Table2 has three fields
one for GROUP, one for BEGIN, and one for END. What I would like to do is
display fields from Table1 including CODE and the GROUP field from Table2
where CODE from Table1 is between BEGIN and END.

All fields are numbers stored in text fields 6 characters in length. All
codes will exists between one of the BEGIN-END pairs. There are 20 records
in Table2 to define all the possible BEGIN-END pairs each with a unique
GROUP character.

Thanks
Bill[/QUOTE]
[/QUOTE]
Click to expand...
 
Back
Top