Finding postcodes from 1 of 2 fields and updating to a 3rd field

[Guest]

I have a very large table (‘000s records) with missing postcodes for certain
streets, I want to be able to query the table, and where it finds postcode in
either one of two fields, [From_Postcode] or [To_Postcode], then find the
match in [Street_Index_Postcode] and update the blank [Updated_Postcode]
field, can someone please help explain how to do this, as I have literally
thousands of streets to correct on this table.

Below is a sample record entry:

StreetNameID StreetName From_Postcode To_Postcode Street_Index_Postcode Run_waypoint Updated_Postcode
9053 Gloucester Road WC2 SW7 SW7 GLOUCESTER ROAD

 

[John Nurick]

This can probably be done with an update query, but you'll need to be
clearer about the rules you want to apply. You say

"where it finds postcode in either ... [From_Postcode] or [To_Postcode],
then find the match in [Street_Index_Postcode] and update the blank
[Update_Postcode] field"

This seems to mean (in VBA syntax)
If [Street_Index_Postcode] = [From_Postcode] _
Or [Street_Index_Postcode] = [To_Postcode] Then
[Update_Postcode] = [Street_Index_Postcode]
End If

If that's what you want to achieve, the update query involves updating
[Update_Postcode] to [Street_Index_Postcode] with criteria on
[Street_Index_Postcode] of
[From_Postcode]
or
[To_Postcode]

I have a very large table (‘000s records) with missing postcodes for certain
streets, I want to be able to query the table, and where it finds postcode in
either one of two fields, [From_Postcode] or [To_Postcode], then find the
match in [Street_Index_Postcode] and update the blank [Updated_Postcode]
field, can someone please help explain how to do this, as I have literally
thousands of streets to correct on this table.

Below is a sample record entry:

StreetNameID StreetName From_Postcode To_Postcode Street_Index_Postcode Run_waypoint Updated_Postcode
9053 Gloucester Road WC2 SW7 SW7 GLOUCESTER ROAD

 

[Guest]

John,

This is my SQL,

SELECT [Street Names].StreetName, Runs.Run_From_Postcode,
Runs.Run_To_Postcode, [Street Names].Postcode AS Street_Index_Postcode,
Run_Waypoints.Run_waypoint_List_ID, Runs.Run_No, Run_Waypoints.Run_Direction,
Run_Waypoints.Run_waypoint, [Street Names].Postcode AS Run_Waypoints_Postcode
FROM Runs INNER JOIN ([Street Names] INNER JOIN Run_Waypoints ON [Street
Names].StreetName = Run_Waypoints.Run_waypoint) ON Runs.Run_No =
Run_Waypoints.Run_No
WHERE ((([Street Names].Postcode)=[Run_From_Postcode] Or ([Street
Names].Postcode)=[Run_To_Postcode]));


It involves 3 tables:

Runs
Run_Waypoints
Street Names

I have managed to get [Street_Index_Postcode] to get a match from either
[Run_From_Postcode] or [Run_To_Postcode] with an 'Or' criteria. and the
results correctly display in the [Run_Waypoints_Postcode] Column. But now, I
need to take that correct column and update the [Postcode] in the
Run_WaypointsTable. If I'm honest I don't really understand the update query
process. I always seem to get every other result, except the correct one. Can
you show me how my SQL should look so that I may paste it into my QBE and see
where I have been going wrong?

This can probably be done with an update query, but you'll need to be
clearer about the rules you want to apply. You say

"where it finds postcode in either ... [From_Postcode] or [To_Postcode],
then find the match in [Street_Index_Postcode] and update the blank
[Update_Postcode] field"

This seems to mean (in VBA syntax)
If [Street_Index_Postcode] = [From_Postcode] _
Or [Street_Index_Postcode] = [To_Postcode] Then
[Update_Postcode] = [Street_Index_Postcode]
End If

If that's what you want to achieve, the update query involves updating
[Update_Postcode] to [Street_Index_Postcode] with criteria on
[Street_Index_Postcode] of
[From_Postcode]
or
[To_Postcode]

I have a very large table (‘000s records) with missing postcodes for certain
streets, I want to be able to query the table, and where it finds postcode in
either one of two fields, [From_Postcode] or [To_Postcode], then find the
match in [Street_Index_Postcode] and update the blank [Updated_Postcode]
field, can someone please help explain how to do this, as I have literally
thousands of streets to correct on this table.

Below is a sample record entry:

StreetNameID StreetName From_Postcode To_Postcode Street_Index_Postcode Run_waypoint Updated_Postcode
9053 Gloucester Road WC2 SW7 SW7 GLOUCESTER ROAD

 

[John Nurick]

You didn't respond to my request for clarification of the rules you wnat
to apply, but I suspect you need to start by creating a SELECT query
that returns the (blank) Updated_Postcode field for the records you want
to update and the minimum of other fields.

But your SQL statement below does not include the Updated_Postcode field
you mentioned in your first message. Is this actually
Run_Waypoints.Postcode? If so, the SQL would be something like this:

SELECT Run_Waypoints.Postcode,
[Street Names].Postcode
FROM blah blah blah
WHERE [Street Names].Postcode=Runs.Run_From_Postcode]
OR [Street Names].Postcode)=Runs.Run_To_Postcode]

Then use the query design grid to change this into an UPDATE query, and
update Run_Waypoints.Postcode to [Street Names].Postcode.

But be careful: I don't fully understand what you're trying to do, so
following my advice will be even more hazardous than usual!

John,

This is my SQL,

SELECT [Street Names].StreetName, Runs.Run_From_Postcode,
Runs.Run_To_Postcode, [Street Names].Postcode AS Street_Index_Postcode,
Run_Waypoints.Run_waypoint_List_ID, Runs.Run_No, Run_Waypoints.Run_Direction,
Run_Waypoints.Run_waypoint, [Street Names].Postcode AS Run_Waypoints_Postcode
FROM Runs INNER JOIN ([Street Names] INNER JOIN Run_Waypoints ON [Street
Names].StreetName = Run_Waypoints.Run_waypoint) ON Runs.Run_No =
Run_Waypoints.Run_No
WHERE ((([Street Names].Postcode)=[Run_From_Postcode] Or ([Street
Names].Postcode)=[Run_To_Postcode]));


It involves 3 tables:

Runs
Run_Waypoints
Street Names

I have managed to get [Street_Index_Postcode] to get a match from either
[Run_From_Postcode] or [Run_To_Postcode] with an 'Or' criteria. and the
results correctly display in the [Run_Waypoints_Postcode] Column. But now, I
need to take that correct column and update the [Postcode] in the
Run_WaypointsTable. If I'm honest I don't really understand the update query
process. I always seem to get every other result, except the correct one. Can
you show me how my SQL should look so that I may paste it into my QBE and see
where I have been going wrong?

This can probably be done with an update query, but you'll need to be
clearer about the rules you want to apply. You say

"where it finds postcode in either ... [From_Postcode] or [To_Postcode],
then find the match in [Street_Index_Postcode] and update the blank
[Update_Postcode] field"

This seems to mean (in VBA syntax)
If [Street_Index_Postcode] = [From_Postcode] _
Or [Street_Index_Postcode] = [To_Postcode] Then
[Update_Postcode] = [Street_Index_Postcode]
End If

If that's what you want to achieve, the update query involves updating
[Update_Postcode] to [Street_Index_Postcode] with criteria on
[Street_Index_Postcode] of
[From_Postcode]
or
[To_Postcode]

I have a very large table (‘000s records) with missing postcodes for certain
streets, I want to be able to query the table, and where it finds postcode in
either one of two fields, [From_Postcode] or [To_Postcode], then find the
match in [Street_Index_Postcode] and update the blank [Updated_Postcode]
field, can someone please help explain how to do this, as I have literally
thousands of streets to correct on this table.

Below is a sample record entry:

StreetNameID StreetName From_Postcode To_Postcode Street_Index_Postcode Run_waypoint Updated_Postcode
9053 Gloucester Road WC2 SW7 SW7 GLOUCESTER ROAD