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?
John Nurick said:
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