Multipart Fields problem 2

[mraetrudeaujr]

I have a problem. I have a shift leader (user) that I cannot convince that it is a data integrity risk to allow this field condition. Also, all of the users are entering their data directly into the table. I have conveyed this to my supervisor (about the urgency to begin using a 'Form' interface), and he understands this need. However as stated above, I have a shift leader that insists on doing all of his searching, editing, and entering of information through the database. All I am doing is looking for a temporary fix in preparation for our transition to an Oracle Server.

First, I would like to have a really good (plain English) reason to give him (them) that explains why this is a bad practice.

Second, how would I go about 'splitting' this multipart information? Obviously I could do it manually, but then we have over 10,000 records that would need this. Is there a query that would parse this out?

Thanks in advance.

 

[lespaul]

Well, the 10 commandments of Access include it, but I'm sure that your user isn't going to just believe it because it came from the database God (oh wait, millions of people...oh never mind), anyway, here's a link to the 10 commandments:

http://www.mvps.org/access/tencommandments.htm

One of the most important reasons is because through a form you can validate the data before it gets to the table, that way you don't store bad data.

Secondly, it allows for standardization of some entries by using lookup TABLES (not fields - see same link above for the evils of lookup FIELDS). I have a database that stores information about jurors. There are several reasons that a person can be excused from jury duty. Instead of just letting the users pick whatever excuse they want to enter, the form looks up the ONLY valid choices and presents them to the users. That way I have control over what information they are putting in the tables.

For splitting the data, what kind of data is it? Is there a standard separator between the pieces that need to be split? What version of Access are you using? Newer versions have more built in functions to accomplish this kind of thing.

HTH

Leslie

 

[mraetrudeaujr]

Leslie,

Thanks for the information concerning database 'commandments'. As far as the field that I need to split;

It is a name (text) field. There are two (last) names separated by a hyphen. This database is built in Access 2000 and is running on a Windows 2000 Professional machine.
If you need anymore information, let me know. I'll await your advice, thank you.

Al

 

[lespaul]

you can use a query like this to split the data:

SELECT Left(FieldName, InStr("-", FieldName) - 1) As NameOne, Right(FieldName, InStr("-", FieldName) + 1) As NameTwo 
FROM TableName

I think that Access 2000 has the Split function, you may want to search the Access Help for that, it may be easier to use.

If you need to create new fields, after you make sure the query works correctly, you can use an update query:

UPDATE TableName Set NewFieldOne = Left(FieldName, InStr("-", FieldName) - 1), NewFieldTwo = Right(FieldName, InStr("-", FieldName) + 1)

HTH

Leslie

 

[mraetrudeaujr]

Hmmm. I think that I have something mixed up here. I substituted what I thought was necessary, but I keep getting an error stating --- "Syntax error in query. Incomplete query clause."

Here is what I substituted;

SELECT Left(FieldName, InStr("-", "LAST NAME") - 1) As LastNameByFather, Right(FieldName, InStr("-", "LAST NAME") + 1) As LastNameByMother 
FROM "2005 LOG"

Now mind you, I didn't create this database; I'm only trying to 'normalize' it.

The table name is "2005 LOG" (yeah, there's a space separating the year from the text!)

The Field Name is "LAST NAME" (there's a space separating the year from the text.)

With the new field names being; "LastNameByFather" and "LastNameByMother"

If you could please plug this in so that I could analyze where I went wrong, I would be very grateful. Thank you.

Al

 

[lespaul]

try this:

SELECT Left(FieldName, InStr("-", [LAST NAME]) - 1) As LastNameByFather, Right(FieldName, InStr("-", [LAST NAME]) + 1) As LastNameByMother 
FROM [2005 LOG]

Leslie

 

[mraetrudeaujr]

You know, I tried using these 'brackets' and it would come up like a PARAMETER Query. I thought I was doing something wrong, so I tried the 'quotes'.

However, your recent SQL query (using the brackets) is doing the same thing. So now what do we try?

This is exactly why I am trying to rework this database. As you can see, whoever built it, didn't follow the 'Ten Commandments of Access'. Thanks again, in advance.

Al

 

[lespaul]

what parameter is it asking for?

Leslie

 

[mraetrudeaujr]

It is asking for "FieldName

 

[willir]

mraetrudeaujr
You will need to substitute the name of the actual field names and table name in your database in what Leslie has supplied.

If your field names include spaces or special characters, encapsulate the field name within [] brackets. Example...
[Last Name]

 

[PHV]

What about this ?
SELECT Left([LAST NAME], InStr([LAST NAME], '-') - 1) As LastNameByFather, Mid([LAST NAME], InStr([LAST NAME], '-') + 1) As LastNameByMother
FROM [2005 LOG]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[willir]

Early start PHV -- wow, you are amazing.

 

[mraetrudeaujr]

Yep. PHV always comes through for me!

Now I have to figure out how I'm going to get this information into the live database. How about if I add these two new fields to the Main table, run this query, and then copy and paste the results of this query into the applicable (new) fields? Then I could delete the 'old' LAST NAME field, right? I realize that any queries or reports that contain this old field will have to be 'updated', or should I just add these fields to the table and 'hide' the old field?

 

[PHV]

Make a copy of your table (just in case ...)
In table design view add the 2 new fields
Create an update query:
UPDATE [2005 LOG]
SET LastNameByFather = Left([LAST NAME], InStr([LAST NAME], '-') - 1)
, LastNameByMother = Mid([LAST NAME], InStr([LAST NAME], '-') + 1)
WHERE [LAST NAME] Like '*-*';

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mraetrudeaujr]

PHV, I tried the SQL statement above and it is returning a Parameter prompt "LastNameByFather". I'm trying to figure out why it is doing this, when your previous SQL query worked perfectly. I really would like to try out one of these update queries because it is new for me. I'll await your analysis. Thanks in advance.

Al

 

[PHV]

In table design view add the 2 new fields
I meant LastNameByFather and LastNameByMother

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mraetrudeaujr]

Okay. When I added the two new fields to the table, it updated the table perfectly! Thanks again.

Now, I also discovered that the First name and the Middle name are combined. So, in order of process; add the two new fields 'FirstName' and 'MiddleName' to the table, change your SQL to reflect this, and it should work --- right? Here goes!

Al

 

[mraetrudeaujr]

Nope. It didn't work. Here is what I 'modified', thinking that it should work like the "LastName" update query;

UPDATE [2005 LOG] SET FirstName = Left([FIRST NAME], InStr([FIRST NAME], '-') - 1), MiddleName = Mid([FIRST NAME], InStr([FIRST NAME], '-') + 1)
WHERE [FIRST NAME] Like '*-*';

Can you see where I went wrong? The new fields to be updated are 'FirstName' and 'MiddleName.'

Al

 

[PHV]

Nope. It didn't work
Any error message ? Unexpected result ? ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mraetrudeaujr]

Yes. The pop-up states that there is a 'Syntax error' and highlights the 'Where' operator.