Split address field

[Garry]

Hi all

I have a single address field as follows :-

Mrs Williams, Shruberys, Newtown, St Berriew, Welshpool, Powys, United
Kingdom

Mr Jones, 71 Penny Lane Way, Leeds

Is there a single query I can write to split this field into many

regards, Garry

 

[Guest]

Maybe. If there are the exact same number of commas and what's between each
comma pair means the same, for example before the first comma is always the
name, followed by the street address, followed by the city, and so on, you
could export that field to a text file then re-import it into another table
as a CSV (comma seperated value) file.

But I'm afraid that your example shows this not to be the case.

 

[Guest]

As Jerry said not a single query to do it but you can split up the task by
counting commas, sorting, and flagging.
Add a flag field and separate field for all the parts of the address.
Count the commas like this --
Comma_Count: Len([YourAddressField])- Replace([YourAddressField], ",", "")
Update the flag field with comma count.
Scroll through as verify that all addresses with the same number of commas
have the same layout. If any are differnt then edit the flag field by
adding alpha characters to the count.
Then develop an update for the address part fields that parses the first
address.