Assigning postal code from definitions table based on address elem

[Guest]

Access 2003. Working with Canadian Postal Codes. Have Postal Code definitions
table. When a new individual is added to the members list by way of a form,
the address components are gathered (Building Number, Street Name, Street
Type, Quadrant, City) The objective is to use the address components to
autoselect (assign) the correct postal code from the definitions table, and
display it in the form as well as add it to the table of members.

The tblPostalCodeDefs looks like this:

City Quadrant StreetName StreetType FromBldng# ToBldng# Side PCode

Vegas SW Main St 1411 1759
Odd T3Y 4Z7
Vegas SW Main Ave 216 380
Even T3Y 4Z7
Vegas NW Happy Ave 240 1260
Even T4G 8L4

Of course the defs table has thousands of similar records.

I am very concerned with speed and efficiency. The approach I am considering
is to create successively smaller recordsets from the defs table until a
recordset of one record is created. I would create these successive subsets
by filtering accross the fields of the tblPostCodesDefs from left to right.
The parameters would come from the controls in the member entry form. The
PCode in the last recordset would be chosen, displayed on the form and
assigned to the member. A mis-typed or recorded address will produce an error
message to the operator if a recordset of one record cannot be obtained.
There is also opportunity to display the smallest (last) recordset if the
correct Postal Code remains ambiguous.

Is my approach sound or is there a better approach for this task? Any
critique of my approach or suggested alternates with coding suggestions
would be appreciated.

As always thanks in advance.

 

[Rob Oldfield]

I don't see what you're hoping to achieve by using 'successive subsets' -
there's not going to be much point in creating a subset containing all the
records from a single city.

In general terms I don't see much wrong with what you're planning, except
that I think I'd do it the other way round. First, filter by the all the
data entered by the user...
- if that returns a single record then you're sorted
- if it returns multiple records then handle that by checking with the user
- if it returns no records then maybe remove one of the filter criteria
(side, maybe?) and try again.

And one other thing... make sure that the tblPostalCodeDefs is thoroughly
indexed. You'll definitely need a multiple field index containing all the
fields you're searching on, and possibly other ones. Given that that table
is going to be pretty much static, I don't think you can have too many
indices on it.

 

[John Nurick]

If the postcodes are sufficiently fine-grained it would make more sense
to have the user type the postcode and use that to look up the City,
Quadrant, Streetname and StreetType and populate the corresponding
address fields. The user would normally then just have to enter the
street number (which you can validate by a further lookup against
FromBldng#, ToBldng# and Side) and perhaps building name and apartnment
number.

This is standard practice in applications working against the UK post
office's PAF (postal address file), and from the look of your sample the
Canadian codes would lend themselves to this treatment.