Partitioning An Address Column

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