Query needed pls urgent

[lee]

Zone Start End city
Zip Zip
2 500 599 jones
7 600 899 jones
3 1000 2499 jones
4 2500 2699 jones
5 500 599 carol
8 600 999 carol
5 1000 12999 carol
4 1300013299 carol
5 1330013999 carol
2 500 599 carol1
2 600 999 carol1
7 1000 6799 carol1

city qty zip ideal
jones 1200 700 carol1
carol 349 540 jones/carol

I have two tables like above in the database. the first table has
fields zone, start zip, end zip and city.
The second table has fields city, qty , zip.

I want a query which can generate the last column 'ideal' in the
second table. This column should look up the corresponding zip and
see what range it falls between start zip and end zip in the first
table. It should search the entire table 1 for that zip, look out for
the lowest zone no and then extract the corresponding city name from
the first table into the last column of the second table.

For example zip 700 in second table falls between 600-899,
600-999(twice) zip range in the first table. But the lowest zone
among these is 2 and the corresponding city is carol1 as noted above.
If we end up two cities with equal and lowest zone, we need to put
both as in second row of second table

Can some one pls help me extracting the last column of the second
table- SQL query- Thanks i will really appreciate it. it is urgent

 

[John W. Vinson]

city qty zip ideal
jones 1200 700 carol1
carol 349 540 jones/carol

I have two tables like above in the database. the first table has
fields zone, start zip, end zip and city.
The second table has fields city, qty , zip.

Lee, posting the EXACT SAME QUESTION for the fourth or fifth time, while
apparently not understanding or following the many answers you have received
is getting very tiresome.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[lee]

Lee, posting the EXACT SAME QUESTION for the fourth or fifth time, while
apparently not understanding or following  the many answers you have received
is getting very tiresome.
--

             John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:http://social.msdn.microsoft.com/Fo...al.answers.microsoft.com/Forums/en-US/addbuz/
and see alsohttp://www.utteraccess.com

Sorry for posting again- but i am getting compile errors on
GetIdeal(zipcode) within the module.
Is there any other methodology to get the same solution. Please advise