Re: Newbie question regarding text strings
From: Brett Collings [429338] (bac_at_nomail.please.nz)
Date: 10/31/04
- Next message: Jeff Boyce: "Re: Design view input????"
- Previous message: Brett Collings [429338]: "Re: Query question"
- In reply to: V Chandra: "Newbie question regarding text strings"
- Next in thread: John Spencer (MVP): "Re: Newbie question regarding text strings"
- Messages sorted by: [ date ] [ thread ]
Date: Sun, 31 Oct 2004 22:54:59 +1300
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
On Sat, 30 Oct 2004 22:39:54 -0400, V Chandra
<vikramchandra@comcast.net> 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
- Next message: Jeff Boyce: "Re: Design view input????"
- Previous message: Brett Collings [429338]: "Re: Query question"
- In reply to: V Chandra: "Newbie question regarding text strings"
- Next in thread: John Spencer (MVP): "Re: Newbie question regarding text strings"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
