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)

 

[David F Cox]

It is possible to have lots of postcodes that correspond to a single street.
It is possible for the postcode of a street to have changed. Postcode life
in the U.K. is not simple.

 

[efandango]

David, yes, i'm aware of the perils of the UK postal coding system.
To clarify, I want only the 'main postcode' for a given sheet, as per my
example fields.

With that in mind, I will attempt to explain more clearly what I want to do:

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?

It is possible to have lots of postcodes that correspond to a single street.
It is possible for the postcode of a street to have changed. Postcode life
in the U.K. is not simple.

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)