Re: Newbie question regarding text strings

• Previous message: TerryGB: "Access: How can I generate an auto number for each record in a ta."
• In reply to: V Chandra: "Re: Newbie question regarding text strings"
• Messages sorted by: [ date ] [ thread ]

Date: Tue, 02 Nov 2004 22:19:14 +1300

>The SQL query you give sounds great. I may just end up doing multiple
>queries one after the other just to get the data in the right format.
>I can worry about getting rid of duplicate data later (same author
>appears for many different articles).

Ahh, ok, before you split out the names, run the database analyzer.
It will detect just that and it will split the authors from the
articles. The relationship between these two tables is that (One)
Author has (many) articles and the relationship will be on AuthorId.

As always, do all this testing on a copy of the database until you get
the result that you want. THEN do all the name splitting.

One of the easiest ways is to use word's replace ability
- create the new field names in the same table
- create a Select Query (the default one) with just the AuthorID and
AuthorName fields
- export the Query result to Word as a .csv file
- Use Ctrl-H to search and replace (*) with a comma
- there should already be a comma between the ID and name fields
- save as a .csv file
- Import the .csv back into Access as comma delimited new table
- create a new Select Query, add the old table into the top pane as
well as the new one
- drag down the OldTable.ID field and the three new name fields
- change to an Update Query type
- in the ID field Criteria line enter =[NewTable].[AuthorID]
- in the Update To space for each of the new name fields put the
corresponding field name that you just imported from Word ...
       =[ImportedTable].[fname],
       =[ImportedTable].[mname],
       =[ImportedTable].[lname]

That sounds like a lot of messing around, but I just spend half an
hour messing around with trying to parse that [AuthorName] field into
3 names and got sick of it without a result I was happy with. I think
this is quicker and Word has better search and replace than Excel.

Just a warning that this is air code so try it on a copy, I may have
missed a step ... but you'll work it out

Brett

On Mon, 01 Nov 2004 11:03:38 -0500, V Chandra
<vikramchandra@comcast.net> wrote:

>Many thanks to both of you for helping in this. This is indeed a one
>time project.
>
> I do plan on using Industry Best Practices to get the data into the
>Fname, Lname, Street, City, St, etc.
>
>The prolem is legacy data - the data right now is in an Access
>database in the format I gave (Name1(*)Name2(*)) and same for
>addresses with delimiters. One plan of attack was to export the two
>fields to Excel and then use replace All (*) with * and then Text to
>table to convert them into single rows with each author and address in
>a column of its own. But then I run into the problem of re-importing
>them into Access in the right format.
>
>The SQL query you give sounds great. I may just end up doing multiple
>queries one after the other just to get the data in the right format.
>I can worry about getting rid of duplicate data later (same author
>appears for many different articles).
>
>Many thanks,
>Vijay Chandra
>
>On Sun, 31 Oct 2004 08:46:18 -0500, "John Spencer (MVP)"
><spencer4@comcast.net> wrote:
>
>>IF this is a one time project, you are basically there.
>>
>>STEP ONE: Make a copy of your data in case this fails.
>>STEP TWO: Make a copy of your data in case this fails.
>>
>>STEP THREE: Run your query.
>>
>>STEP FOUR: Now run a second query (an update) that strips out the data you have
>>imported. UNTESTED SQL statement included below.
>>
>>UPDATE Temporary_Author_Table
>>SET Author = Strip(Mid([AUTHOR],InStr(1,[AUTHOR],"(*)")+1)),
>>AUTHOR_ADDRESS = Strip(Mid([AUTHOR_ADDRESS],InStr(1,[AUTHOR_ADDRESS],".")+1))
>>FROM Temporary_Author_Table
>>
>>Repeat Step Three and Step Four as needed
>>
>>If you need to do this frequently, you will probably want to use a VBA routine
>>to work on the recordset or to repeatedly execute the two queries.
>>
>>V Chandra wrote:
>>>
>>> Hello all,
>>>
>>> A simple question probably, I just don't know where to look it up.
>>>
>>> I have a table with two fields - Author_name and Author_Address.
>>> example given below.
>>>
>>> AuthorName: Abel (*) Baker (*) Charlie (*) ' delimiter is
>>> paren-star-paren
>>> Authoraddress: Abeladdress. Bakeraddress. Charlieaddress. '
>>> delimiter is period
>>>
>>> All I want to do is to create a new table with the fields - AuthorID
>>> (autonumber), Authorname, authoraddress.
>>> Each row must have only one author and that one author's address.
>>>
>>> How do I go about creating a query to do this?
>>>
>>> What I have so far is :
>>> SELECT Left([AUTHOR],InStr(1,[AUTHOR],"(*)")-1) AS Name,
>>> Left([AUTHOR_ADDRESS],InStr(1,[AUTHOR_ADDRESS],".")-1) AS Address
>>> FROM Temporary_Author_Table;
>>>
>>> Which gives me the first name and first address for each row and thats
>>> it. It skips subsequent authors in a row.
>>>
>>> Any advice is much appreciated. Please email if possible to
>>> VJ@VChandra.com
>>>
>>> thanks in advance
>>> Vijay Chandra

Cheers,
Brett

----------------------------------------------
Be adventurous, be bold, be careful, be a star
----------------------------------------------
Brett Collings
Business & Systems Analyst
Management Information Systems Developer

• Previous message: TerryGB: "Access: How can I generate an auto number for each record in a ta."
• In reply to: V Chandra: "Re: Newbie question regarding text strings"
• Messages sorted by: [ date ] [ thread ]