Creating fields for each record in a field

[Guest]

I have a data file that is imported into my database via a macro. The data
file consists of two parts: the top half is the customer's information, the
bottom half is the data for the database. The bottom half gets separated off
and dealt with later. The top half contains the customer name, address, city
state zip, and account number, along with a bunch of garbage data. I've
figured out how to get the data formatted down to this:

Field1 Field2
Account Number Customer Name
Address
City State Zip

Herein lies the problem. How do I get the address and the city/state/zip out
of Field2 and have a single record similar to this:

Field 1 Field 2 Field 3
Field 4
Account Number Customer Name Address City State Zip

I'm sure it's possible... I just don't know how to do it. Any help is
greatly appreciated!

 

[Rick B]

It is possilbe, but would be very difficult. You would have to look for
spaces and assume certain 'words' went to certain fields. Since the name
field coulf contain one space, two spaces, or more it would be impossible to
do reliable. The Address is even harder.


Billy Bob Smith 123 East Main Street, Apt 123 Houston, TX 77377
Billy Smith 11515 Hollister St. Paul, MN 55888
Bill H Mc White 14500 Main Street East


etc.


If you can break out the customer number before import, I'd suggest you take
similar steps to break out the other data.

 

[Rick B]

Also, CITY STATE ZIP is three different pieces of data. I would not
recommend storing it as "Field4".

Database normalization would dictate that each separate piece of data is
stored in a separate field. You can combine them when needed in reports
using an unbound text field and the "&" operator,

 

[Rick B]

Ditto for FirstName and LastName

 

[Guest]

tI know what you're getting at as far as City/State/Zip, but I'm only using
it as a text string for a report, nothing more. I was typing all these fields
in manually but I'd rather do it automatically. That way when I am out sick
other people can handle the process without knowing all it's quirks.

And I can't change the format of the incoming data file. It just happens to
have the account number off to the side away from the name and stuff. If I
could break the incoming file down differently it would help, but I don't
know what to do to do that.

 

[Guest]

I would export to Excel and insert a new column. Column B is now the Account
number column. Fill new column starting with second row --
=If(B2="",A1,B2)
Copy column A. Paste Special - Values.

 

[Guest]

If you want to put all the information in one record do the above plus this --
Insert a new column. Put 1 in A1, Autofill strating with A2 --
=If(b2<>b1,1,a1+1)
This will give an incrementing number for each row of same account.

Next put this data in Access and run a Crosstab query to pull it all together.