Newbie question regarding text strings

  • Thread starter Thread starter V Chandra
  • Start date Start date
V

V Chandra

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
(e-mail address removed)

thanks in advance
Vijay Chandra
 
Good going so far for a newbie however I would strongly counsel
against blending all the names and addresses into one field.

In databases, these elements are kept seperate to make it easy to
find, query or use them. As you have found with your query, it
becomes a nightmare to break them up using delimiters.

I'd suggest you use the industry Best Practice structure so that your
database can talk to others at some time in the future. Try this for
a structure

tblAuthor
======
AuthorID Autonumber Primary Key
AuthFname Text First Name
AuthMname Text Middle Name or inital
AuthLname Text Last Name
AuthAdd1 Text Apt/House No & Street
AuthAdd2 Text Suburb
AuthCity Text City Name
AuthZip Text Can eventually be populated
using the City field or
manually entered

Use your delimiters to break your data into bits using InStr(), Len(),
Right(), Mid() and Left(), make a SELECT query.
and then when you're happy with the result, create a Make Table query
out of it. Just writing that query is a nightmare, and so it will
continue for the life of your database unless you do it now.

I just tried to do an example for you but it's late Sunday night and I
just didn't have the time to work it all out, sorry.

Cheers
Brett

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
(e-mail address removed)

thanks in advance
Vijay Chandra
Click to expand...

Cheers,
Brett
 
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.
 
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

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 said:
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
(e-mail address removed)

thanks in advance
Vijay Chandra
Click to expand...
Click to expand...
 
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).
Click to expand...


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









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

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 said:
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
(e-mail address removed)

thanks in advance
Vijay Chandra
Click to expand...
Click to expand...
Click to expand...

Cheers,
Brett
 
Back
Top