Re: Decatenate a field with Make Table Query?
- From: Vincent Johns <vjohns@xxxxxxxxxxxxxxxxxx>
- Date: Sat, 05 Nov 2005 03:22:16 GMT
sweeneysmsm wrote:
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-4567Check 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 <vjohns@xxxxxxxxxxxxxxxxxx> Please feel free to quote anything I say here.
"[MVP] S.Clark" wrote:
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 :D", 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!
-- Steve Clark, Access MVP http://www.fmsinc.com/consulting
"sweeneysmsm" <sweeneysmsm@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:B78265A6-FA18-4519-9A79-CAF57C504877@xxxxxxxxxxxxxxxx
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
.
- Follow-Ups:
- Re: Decatenate a field with Make Table Query?
- From: sweeneysmsm
- Re: Decatenate a field with Make Table Query?
- Prev by Date: Re: Linking date data types in a query
- Next by Date: Re: Count events
- Previous by thread: Re: Linking date data types in a query
- Next by thread: Re: Decatenate a field with Make Table Query?
- Index(es):
Relevant Pages
|
