SQL Help....

[MkIII_Supra]

Okay I am working in Access 97 using SQL with VBA to create and modify some office apps. What I have is 27 various tables that have data embedded in the following format:

Field name = fullName
Smith, Jason T

What I am trying to do is write a query to break the one field <see above ex.> into 3 separate fields.

Here is the code I have so far:
INSERT INTO tblSimaPrime (ssN, lName, fName, mI, rate, shopId, chkIn)

SELECT SSN, SUBSTR(fullName, 1, (INSTR(fullName, ",", 1, 1)-1), SUBSTR(fullName,(INSTR(fullName, ",", 1, 1)+2),

(INSTR(fullName, " ", 1, 2)-INSTR(fullName, ",", 1, 1)+2), SUBSTR(fullName, -1, 1))), rate, shopId, chkIn

FROM tblPrime;

I am using the SUBSTR and INSTR functions to look for a comma starting at position one.

SUBSTR(fullName, 1, (INSTR(fullName, ",", 1, 1)-1)

This will count up to the comma and give us the number of characters which in this case is 6 characters then -1 to remove the comma from the data string and return in this case Smith

The next SUBSTR / INSTR was a bear to figure but here it is:

SUBSTR(fullName,(INSTR(fullName, ",", 1, 1)+2),

(INSTR(fullName, " ", 1, 2)-INSTR(fullName, ",", 1, 1)+2)

The first segment…

SUBSTR(fullName,(INSTR(fullName, ",", 1, 1)+2),

is basically the first SUBSTR / INSTR but changed to add 2 characters in stead of subtract one. This will give the starting point for the second portion of the name. Then I take the results of the above example and subtract it from another SUBSTR / INSTR I run to look for the break between the first and last name.

(INSTR(fullName, " ", 1, 2)

The above segment looks to find the next set of characters based on a blank character <space>. It gets it's starting point from this segement of the query:

SUBSTR(fullName,(INSTR(fullName, ",", 1, 1)+2),

now the second portion of

(INSTR(fullName, " ", 1, 2)

takes the results and subtracts them from the first SUBSTR / INSTR to give the character length to be queried, which in this case would be 5 Jason.

(INSTR(fullName, " ", 1, 2)-INSTR(fullName, ",", 1, 1)+2)

The last segment of the query is easy as to the fact it finds the last character by starting at the very end of the sting then moving back 1 position.

SUBSTR(fullName, -1, 1)

Now that I have thouroghly explained what this SQL statement does and how I am using it here is my problem.

I keep getting an error message "Number of query values and destination fields aren't the same

I have the insert statement worded correctly as well as the select statement.

Here is the query field relation:

INSERT INTO tblSimaPrime (ssN = SELECT SSN

INSERT INTO tblSimaPrime ,lName = SELECT SUBSTR(fullName, 1, (INSTR(fullName, ",", 1, 1)-1)

INSERT INTO tblSimaPrime ,fName = SELECT SUBSTR(fullName,(INSTR(fullName, ",", 1, 1)+2),
(INSTR(fullName, " ", 1, 2)-INSTR(fullName, ",", 1, 1)+2)

INSERT INTO tblSimaPrime mI = SELECT SUBSTR(fullName, -1, 1)))

INSERT INTO tblSimaPrime rate = SELECT rate

INSERT INTO tblSimaPrime shopId = SELECT shopId

INSERT INTO tblSimaPrime chkIn = SELECT chkIn
So as you can see I have gone through this and I can't figure it out. My instructors are stumped as well. I can't seem to find any info on this with my current resources, although I am still browsing the net.

Send your possible solutions to mcdonaldjt@simasd.navy.mil

[MkIII_Supra]

bump... :confused:

[nanode]

I ran thru yer code real fast, but typically inserts go like so:

INSERT INTO table_name ("first_name", "last_name") values("bob", "smith");

Can't you break up what you're doing into bite size steps? It might seem a little cumbersome, but you'd at least be able to identify WTF you're doing.