I got imported to me a field that has city, state and zip all in one field. I don't know code and am a self starter. How do i dissect and break this up to it's rightfull 3 fields. MOST of the cities have a comma after it. There are about 4600 records to do. Please help!
dissecting a field
- Thread starter roley
- Start date
There is no pattern. Some city have a comma some don't some state or NY some are N.Y. some are New York some zips are 12345 and some are 12345-1234> It really is a mess. I got two fields like this to do. Apparently over the years people added this to line 4 or Line 5 with no rhyme or reason in how they put it in.
- Local time
- Today, 08:53
- Joined
- Feb 19, 2002
- Messages
- 47,572
No single query is going to clean this up. Start by splitting the data into three columns as best you can. Then do totals queries for each field that will give you a list of possible values. If you are limited to NY, you're lucky. If the addresses can be from any where there will be a lot more work.
Select State, Count(*) As ThisValueCount
From YourTable;
This should show
NY 45
N.Y. 102
New York 99
N Y 4
etc.
You can then make this query into a make table query. Create an update query that joins to this temp table on the state code and replaces State with the value you want to end up with.
You'll need to do this for all the columns. The biggest problems will be with multi-word cities where there is no comma separating the city name from the state.
If this is a one-time effort it will probably only take a couple of days. If it is a recurring conversion problem, I don't have any good suggestions except to attempt to clean up the source so the import can be cleaner.
Select State, Count(*) As ThisValueCount
From YourTable;
This should show
NY 45
N.Y. 102
New York 99
N Y 4
etc.
You can then make this query into a make table query. Create an update query that joins to this temp table on the state code and replaces State with the value you want to end up with.
You'll need to do this for all the columns. The biggest problems will be with multi-word cities where there is no comma separating the city name from the state.
If this is a one-time effort it will probably only take a couple of days. If it is a recurring conversion problem, I don't have any good suggestions except to attempt to clean up the source so the import can be cleaner.
Similar threads
