Simplifying Data

[Gunti]

Hi,
I've got a problem.

I'm creating a form for people to fill in the postal code. Whereafter they
can see which names belong to this number (in a company).

Basicly, i need to put this into access:

Any number between with 2000 & 2299 -> A
Any number starting with 25 -> B
Any number between 2600 & 2899 -> C

This is, of course, just an example.

Postcal codes consist of 4 digits.

Is there any way around creating an excel file which states every single
value??

 

[Allen Browne]

It is possible to create a lookup table with ranges. You can use the Between
operator in the JOIN of a query, but there are disadvantages, e.g.:
a) Access can't show the query graphically.
b) Results may be read-only.
c) It takes a bit of work to set this up normalized, so there can't be
overlapping ranges.

If you want to go that route, see Tom Ellison's article:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html

Australian post codes are similar to what you describe. What I prefer to do
is to let the user enter the place name (since that's what they tend to
know), and look up the postcode. Details in:
Combos with Tens of Thousands of Records
at:
http://allenbrowne.com/ser-32.html

For a listing of postcodes, see:
http://allenbrowne.com/ser-65.html

HTH