Need some help with working on data

[Guest]

I am very new to VB coding syntax. I have to download data from a website.
It is excel format. I need to import this table, separate the name fields in
order to match records for ID numbers. These records come without ID
numbers. I end up putting them in manually, 'cause I can't figure out how to
separate the names. It would be so nice to click a button and have the work
done for me. I am trying to code a button to accomplish this. Then I have
to take the individual responses
and add them up and average them to report on them. I have 198 records, so
far and I won't need all of them every month. I will continue to try. : )

Here is a sample of import data:
Name, Completion Date, Qx1, Qx2, Qx3, Qx4, Qx5, etc up to 30
Smith, Al | 3/1/2007 | 3 | 1 | 2 | 2.5 | 1.7 |

Table that I report from:
ID, Lastname, Middle, Firstname, Qx1, Qx2, Qx3, Qx4, Qx5, etc up to Scor1,
Scor2,
3208 | Smith | | Al | 3 | 1 | 2 | 2.5 | 1.7 |
Scor3, Scor4, Scor5, Scor6

I would really appreciate any help I can get!

 

[Tim Ferguson]

These records come without ID
numbers. I end up putting them in manually, 'cause I can't figure out
how to separate the names. It would be so nice to click a button and
have the work done for me.

You seem to have posted similar messages in lots of different groups:
this is not usually a good idea since it means lots of people duplicating
the work to make answers, and also dilutes the audience. Cross-post if
you must; multiposting is always bad.

If you can guarantee that there is always exactly one comma, you can
split the names like this:

dim newNames as Variant
newNames = Split(originalNameString, ",")
lastName = newNames(0)
firstName = newNames(1)

If you can't guarantee that, then you will have to resort to Instr() and
Left() and Right() according to whatever rule you have for finding the
correct comma to split on. Over to you.

To me it seems that the entire project is broken and you need to talk to
the people that provide the files. You refer elsewhere to an ID field
that is provided "from another table". There does not seem to be any
reliable identifier in this row

Smith, Al | 3/1/2007 | 3 | 1 | 2 | 2.5 | 1.7 |

to distinguish it from all the other Al Smiths, nor to match it correctly
to Alan Smith in the other table.


Best of luck


Tim F

 

[Guest]

The lack of identifier in import table is exactly the problem. I need to
separate name and enter ID into record. The good thing is we only have 40
residents, so our only dup lastname has a different firstname. Just need the
first letter in that case. Thank you so much for your ideas, but I am still
stuck when it comes to the syntax of the procedure or function or sub. I
don't know where to begin. I have tried an update SQL statement with no
luck. The code gets stuck on the word 'update'.
Thank you for your help, I will continue to try different things in order to
succeed.

 

[Tim Ferguson]

Subject: Re: Need some help with working on data

The lack of identifier in import table is exactly the problem.

Sometimes you just have to go back and teach some basic computer skills
to whomever is providing a list of names without identities.

so our only dup lastname has a different firstname.

.. so far! You don't have a safe system. Please tell me nobody is going
to get the wrong medication or a kidney removed if this goes wrong.

I am still stuck when it comes to the syntax of the
procedure or function or sub. I don't know where to begin.

It all depends... on exactly what form the incoming data are and what
data you have already. It's easy enough to write a routine to split out
the halves of a name. You can make a SQL query like

INSERT INTO MyTable (FName, LName, SomethingElse)
SELECT (LEFT(FullName,INSTR(FullName,",")-1),
MID(FullName, INSTR(FullName,",")+1),
SomethingElse
FROM IncomingTable


To check for all contingencies, the SQL can look very ugly indeed; but
then again the user never sees it so it doesn't matter.

My condolences: you seem to have been the recipient of a "hospital pass".

Tim F

 

[Guest]

Thank you for trying to help, but I got the answer elsewhere:

If Not rsFtbl.EOF Then rsFtbl.MoveFirst
Do While Not rsFtbl.EOF

strFSubnm = rsFtbl!FSubName
strFSubLst = Left(strFSubnm, InStr(strFSubnm, ",") - 1)
strFSubFst = Trim(Mid(strFSubnm, InStr(strFSubnm, ",") + 2))
strFFirt = Left(strFSubFst, InStr(strFSubFst, " "))

rsFtbl.Edit

If strFFirt = "" Then
rsFtbl!FSubFirst = strFSubFst
Else
rsFtbl!FSubFirst = strFFirt
End If

rsFtbl!FSubLast = strFSubLst

rsFtbl.Update
rsFtbl.MoveNext
Loop



No kidneys involved : )