Decatenate a field with Make Table Query?

[sweeneysmsm]

I am trying to fix up a poorly designed Access database.

One of the tables has a Next of Kin field in which the typical entry looks
like this:

Mary Smith



Next of Kin:

Notify:



Mr. & Mrs. John M. Smith

(Parents)

(live with Son,Gerard)



Mr.Gerard H. Smith (brother)

1234 Brookside BLVD.

Kansas City, MO 12345

Tel: 1-816-123-4567

Mrs. Regina Thomas (sister)

123 Beech St.

Worcester, PA 12345

Tel. 1-215-123-4567

Within the field there are paragraph entries.

I exported it to Excel in hopes of deleting some of the extraneous material
and then separating the data into columns using Text to Columns.

In the Excel cell the paragraph marks show up as bangs; in the formula field
at the top the bangs show up as tiny squares.

When I try to to do a Find and Replace, I can delete text, but I am still
left with the bangs. I also can’t deal with the Text to Columns because of
the bangs.

One of the suggestions I received was to use a Make Table Query to parse the
data in Access rather than bringing it to Excel. I am familiar with Make
Table queries but not in terms of parsing the data in a single field to
multiple fields. I know how to concatenate but have not been able to find
help in decatenating.

Thank you for any insight given. I am trying to avoid the manual data entry
route as there are many records.

Mary

 

[[MVP] S.Clark]

Wow, I hope those aren't real names, addresses, and phone numbers!

The following functions can help you in your quest:
Left$()
Right$()
Mid$()
Instr()
InstrRev()
Len()

Len("abc") = 3
Left$("Steve Clark is the greatest MVP that there ever was ", 5) = "Steve"
Mid$("I said he is a great MVP", 11, 2) = "is"
Right$("That's just crazy", 5) = "crazy"

See the Access Help file for More Thrills and Excitement!

 

[sweeneysmsm]

No, Steve, they are not real names.

Thank you for your reply. The functions you suggest would work only if all
of the information had the same number of characters and identical spacing.
Unfortunately this is not true. This, I suppose, is why it is a disaster to
use fields for purposes they are not designed for. So, I guess someone will
have to decide whether or not they want to type in huge amounts of data.

Thank you for your help.

Mary

 

[Vincent Johns]

No, Steve, they are not real names.

Thank you for your reply. The functions you suggest would work only if all
of the information had the same number of characters and identical spacing.
Unfortunately this is not true. This, I suppose, is why it is a disaster to
use fields for purposes they are not designed for. So, I guess someone will
have to decide whether or not they want to type in huge amounts of data.

Thank you for your help.

Mary

I'm not sure anyone needs to retype the stuff, but you might have to
move things around. When I have to play with files like these, I often
convert the file to text (*.TXT type) and use a favorite text editor to
line them up in appropriate columns. You can probably do something
similar in MS Word, lining them up using tab characters in ordinary
paragraphs (instead of using a Word table).

I would proceed this way: First of all, paste all of the data into a new
Word document. You can use Word to delete lines that contain only white
space, and you can delete repeated paragraph marks. Also, make
paragraph marks and tabs visible. I would define a special (for this
purpose) paragraph style that would include the tab settings for this
operation, but would not save it in the global Word template, as you'll
likely never need this style again, so leave "Add to Template"
unchecked, but check "Automatically Update" (so that changes you make to
tab settings will apply to all the paragraphs).

You can type field headings separated by tabs, such as

Name<tab>Next of Kin: Notify:<tab>Relation
<tab>Notes<tab>Address<tab>City
<tab>Telephone

(I had to fold this long line to fit this message, but I'm suggesting
that you put it all into one long line in Word.)

Then, in each line, you can insert the tab characters in the appropriate
spots, such as

Mary Smith<tab>Mr. & Mrs. John M. Smith<tab> (Parents)
<tab>(live with Son,Gerard)

Mary Smith<tab>Mr.Gerard H. Smith<tab>(brother)
<tab><tab>1234 Brookside BLVD.<tab>Kansas City, MO 12345
<tab>Tel: 1-816-123-4567

Check to be sure that all the addresses are in the Address column, etc.
If you expect that you might later want to separate first names from
last names, do it now, setting up separate columns for both fields.
It's probably easier to take care of it in Word than to have to split
the field later in Access.

This will work best if you can squeeze everything in one record onto one
line. (If not, you could use 2 lines, but you'd need to add some kind
of key value to each line that you could later use to link the records.)
You can use teeny tiny 8-point Arial Narrow type and a wide (22 inches,
or whatever the maximum is for Word) page to let you cram lots of stuff
into a line. It won't matter that you can't read it when you fit the
page to your screen; you can set the View --> Zoom value to 150% or
whatever makes sense, to make it easier to read.

If some value is too long to fit in the space you've allotted, you can
change the tab settings in all the paragraphs so you can line them up.

Since, in your example, several records apply to "Mary Smith", I suggest
that you copy that name into the first field of each line, so that you
can later keep the records together. (That's the easiest way I can
think of, but you might prefer some other technique, such as assigning a
key value to each record.) Later, in Access, you'd probably use an
Autonumber field to do this, but I think that that's not practical here.
And if you have two Mary Smiths, I suggest you call one of them "Mary
Smith_2", until after you've finished copying everything. You can
straighten that out later, in Access.

Having reformatted all the lines so that the names (for example) are all
in the same tab-delimited column, save the file first as a Word document
(which you'll need if something goes wrong), then rename it and save it
as Text Only With Line Breaks (for Access to read).

In Access, use File --> Get External Data --> Import... to read your
text file. Tell Access that it's tab delimited, and that the first
record contains the field names.

If the lines are too long for Access to import (I'm not sure what the
limit is; it might be 255 characters), you'll need to do this in stages.
Since you saved the Word document file, load and rename it and delete
some of the fields (but keeping the primary name and the next-of-kin
name), to shorten the lines, then save it as text and import it into
Access. Then do the same thing again but delete other fields, and
import those into another Access Table. (But I hope that won't be
necessary.)

None of this should involve any retyping. Even copying the primary Name
you can do via copy-and-paste operations in Word.

As you do all this, please be sure to make frequent backups, especially
just before you do any global search-and-destroy operation. (Those are
sometimes difficult to undo, and you can always erase your temporary
backup files later.)

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[sweeneysmsm]

Dear Vincent,

Thank you so very much. You took a lot of time and trouble to come to my aid.

I will print it out and give it a shot and let you know how I go. I am
paranoid for starters so will definitely back up frequently. It probably will
take me some time, but I will get back to you on this.

I appreciate your help very much.

Mary

 

[Vincent Johns]

Dear Vincent,

Thank you so very much. You took a lot of time and trouble to come to my aid.

I will print it out and give it a shot and let you know how I go. I am
paranoid for starters so will definitely back up frequently. It probably will
take me some time, but I will get back to you on this.

I appreciate your help very much.

Mary

Doing what I suggested takes some work, but I think not nearly as much
as retyping an entire list, and you're less likely to introduce typing
errors this way. Good luck.

And BTW I'm also superstitious about backups -- I believe it's bad luck
not to make them frequently.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.