Subject: transferring address postcodes to another table

[efandango]

I have two tables. The first is a masterlist of 25,000 city street names,
with correct postcodes. The second table has 1,340 streetnames, but no
postcodes. I want to be able to ‘transfer’ the postcodes from the masterlist
to the ‘live’ list table with the minimum of hassle.

The masterlist has the identical street names that I am after, but in some
instances, with a different postcode. This really is the crux of the problem.
Ideally what I want to be able to do is create a table, with a lookup combo
for the postcode by referencinf the Masterlist table, where I can ‘call up’
the erroneous address with the correct postcode, such that it will fill in
the postcode field. How could I achieve this?

Below are the two table examples. Both tables have autonumber indexes, and
are unrelated.

(Table) Masterlist:

Example Field
[StreetName] [Postcode]
Green Lanes N8
Green Lanes N4
Green Lanes N21
Green Lanes N16
Green Lanes N15
Green Lanes N13


(Table) Waypoints: Example Field
[Run_waypoint] GREEN LANES
[Poscode] (Blank field)

 

[Phil Smith]

Something to be aware of: It is actually not uncommon to have the same
street name in the same city, with seperate postcodes, here in the US.
Probably the same situation wherever you go. Sometimes the same street
name is used for more than one street, sometimes a street is long enough
or positioned such that it straddles more than one.

With that said, I am not sure confused about what your problem is. Is
it a case where you have more than one street-postcode combination for a
given street, and have to choose which one to use? How do you decide?

It seems like ths is more than a simple look up the post code in the
master list and update the post code in the waypoints, but I am nore
sure where your problem arises...

 

[efandango]

Hi Phil,

Yes indeed, the same street name in the same city, but with different
postcodes is essentialy my problem; apologies for not explaining it very well
(...exhausted after hours of not getting very far with queries, tables and
combo boxes). I will attempt to explain more clearly:

Table 1 looks like this:

[StreetName] [Postcode]
Green Lanes N13
Green Lanes N8
Green Lanes N4
Green Lanes N15
Green Lanes N16
Green Lanes N21

Table 2 looks like this:

[Run_waypoint] [Postcode]
GREEN LANES empty

How do I get table 1 to populate table 2 to with the postcodes from table 1.

In 90% of the cases, there will only be one postcode for each street, so
that should be a straightforward update situation. It is the remaining 10%
that i want to deal with via a combo box; that will initially match the first
'Green Lanes it finds in its list, and populate the post code to the table 2
'Green Lanes'.

There is a good chance this will be incorrect; which is where i want the
combo to come into play. I will be able to spot the erroneous street/postcode
and correct it with one of the other choices for 'Green Lanes' that the combo
will present in its list.

Is this any clearer?