Merging values from 2 fields into one field

[Lata]

Hi,

I have got a table which contains 2 fields LOW VALUE and HIGH VALUE.
For the LOW VALUE field, it contains value something like B1 and the HIGH VALUE field contains B9...which means it covers all the values in between B1 and B9.
I want to write a query to join this table with another table where it will consider all the values between B1 and B9.

Can someone please help me how to proceed on this one?

Many Thanks.

[pbaldy]

You want what's often called a non-equi join. Create your query with joins between the fields, then go into SQL view and change the = in the join to >= and <= as appropriate. From then on the query will have to be manipulated in SQL view, as the design grid can't represent a non-equi join.

[Lata]

what you suggested makes real sense but what my requirement at the moment is:

I have a postcode table.
The fields in it are customer ID, Low value, high value.

There are values for customerid field, low value field and very rarely there is a value in high value field. when i try to do this nonequi join, it is taking only those records which have got range between low and high and ignoring rest all records. If i dont apply any join(non equi) condition, it is just considering the low value though there is a range for high value...i am trying to comeout with a solution where in it covers all the records that contain only low value and also the records which have got range and all the records have to be covered that are within that range when I do a join with another different table on some common key.

[pbaldy]

You didn't mention that the high value was usually empty. What's it supposed to do when there's no high value? Use the low for both? You might be able to use the Nz() function to replace the high with the low when it's Null.

[Lata]

Thankyou very much...its working with the function you mentioned

[pbaldy]

Happy to help.