Partitioning an address column

[adrian.stock22]

Problem: Turning an address column containing a comma into two or three address colums containing no comma

I have a database with the structure:
company
address
town
postcode (zip code)

The present address column contains one of the following (I give examples)

- 76 Nelson Street

- 99 Bush Lane, Waddington
(where Waddington is a village, or quarter of the town named in the town field)

- Nixon House, 150 Clinton Street, Hareswood

- sometimes there might be a redundant comma at the end of the string e.g.
99 Bush Lane, Waddington,

- very occasionally the address field is empty (when the record is still incomplete


----------------------------------------------

We now have to interact with another company and its software, which takes commas as field delimiters. We therefore have to get rid of all commas in our address field.

I have therefore revised our structure so that it now is:
Company
Addr1
Addr2
Addr3
Addr4 (which contains what was formerly 'town')
Postcode (zip code)

Now I must distribute the contents of Addr1 into Addr1, Addr2, Addr3
and eliminate the commas in the process.

Our database has 4000 records. Therefore doing this manually is prohibitively expensive and takes far too much time. We need the revised database virtually overnight.

My knowledge of Access is fairly limited, but it is ***I*** who has to solve the problem.


Request
------------

Is it possible to do this automatically?

How would you proceed, which steps?

If an Action Query has to be used - well, I have never used an Action Query before (but I have a hefty book about it = "Willing to learn - fast").

If there is a simple formula to be entered into the Action query, could someone please give me the formula?

Thanks for your help.

Adrian

 

[FoFa]

No matter how you approach this there will be manual clean up envolved.
Basically you create a function that you feed the address to, and the element you want returned, and the function returns that. This function typically uses the SPLIT function to parse the string into an array. Your problem is that you do not have a common location to pull from. In your example the villiage can be in position 2 or 3 (or maybe even 4) so how do you know?
So say you create the function ParseAdd, your query would be in the UPDATE TOO field ParseAdd([Address],1) for your new Addr1 field, then the function would feed the first argument basically. After that it gets harder because you don't know what the data is (a real address 2 line or a villiage as an example).

 

[adrian.stock22]

Hi FoFa, E Pluribus Unum, you are one to talk (smiles!), it's "Ex uno plures" what I want.

Seriously, you are in effect telling me that it cannot be done. I would of course be prepared to do a limited amount of manual tidying up.

So what I propose to do is:
1) to replace all commas in the Addr1 colum by semicolons. Does not look nice in print, but we do not normally send out mass mailings. Then, I hope, this table will no longer conflict with the outside company's software.

2) gradually, over the year manually and very gradually, cut and paste the information into the proper Addr1, Addr2, etc columns

I did not, of course, understand every detail of your reply. So just in case it makes any difference to your advice, may I point out the following:

a) We could manually remove all the redundant commas at the end of strings in the Addr1 field.

Terminology:
Let us call a string that has to stay together in whatever address field an "address element" (AE).
An (AE) is bounded either by a comma or by a cell boundary (start of cell or beginning of cell)



Then the following rules would apply:

1 If source cell contains no comma, no splitting required. (This covers empty cells and cells with only one (AE) ).

2 If source cell contains one comma, keep (AE)1 in source cell, and put (AE)2 into next cell (Addr2)

3 If source cell contains two commas, there are 3 (AE)s. Therefore keep (AE)1 in source cell, and put (AE)2 and (AE)3 into cells 2 and 3

(I do not care whether it is a village, or street, etc, as long as I get them into separate cells.)

4 Then we have to provide for the unlikely case that there are more than two commas, i.e. more than 3 (AE)s, in the source cell. In that case, the rule is:
Put (AE)1 and (AE)2 into cells 1 and 2, and dump all the rest into cell 3. (I can later remove commas in cell 3 (Addr3) manually.)

--------------------

Now, I don't know how to use functions in Access, and there is no way in which I can not learn it fast enough under present pressure of work.

But are the above rules rules something that could be implemented in Access?

And would you be able to do it?

How much would you charge?

Or anybody else?

I would do whatever manual preparation is required, you run the function.

I can send the table in Excel format. Pay by Paypal.

Ah, could the job be done more easily in Excel?

-------------------------------------

I can be emailed at the following address:

adrian.stock22@tudo.co.uk

I am getting a lot of spam (all with convincing subject lines suitable for this topic), so put into the Subject line: "Access User Group", so that I do not delete it in error.

Thanks.

Adrian

 

[neileg]

It's not too difficult to split the address into fields using the commas as markers using the split function. I suspect this will be a big improvement over what you have now. I've only used split once or twice but I found Access help good enough to get it working.

 

[micks55]

I've created code for dissecting email bodies that removes carriage returns then puts them back in a uniform manner (different browsers send various bodies) so that a function can extract the data. It's pretty yukky as I'm a novice but it works fine. I'm sure the split method sounds the way to go because of the work load. Does it really matter whether the town is in line 3 or line 4? It would save messing about on reports hiding the empty fields.
Let me know if you want a zipped sample.
Mick

 

[adrian.stock22]

Hi, Mick, Neileg, and everybody,

Thanks for your comments and offers of help. The problem is now solved. Yes, it does not matter in which field the town or village is, as long as it is in a separate field so that the commas can be taken out.

I have been lucky and found a local guy who, last night, wrote a utility in Visual Basic, implementing the five rules which I posted above. It took him two hours to do and works beautifully.

Won't be needed again since, in future, I know about the drawbacks with commas in databases which have to be converted into comma-delimited files, and I'll organise my address structures better from the start.

Thank you for helping me, through discussion, to clarify my mind.

Adrian