Need help with ridiculous query situation.

[mlieberman]

I'm currently the database administrator for a relatively small (less
than 100,000 entries) database. Currently in our field_samples table
we have our unique key a Field_Sample_ID field which consists of a
three letter company code and underscore then a sampling place ID
which is variable in length and alphanumeric followed by an underscore
then a date which is in the form "1.1.1111". So for example a
Field_Sample_ID would look like "SAM_MW-43A_12.5.1982" or in cases
where there's multiple samples from the same place from the same time
"SAM_MW-43A_12.5.1982A". Yes I realize that the setup is terrible form
and makes no sense, but I was not the one who designed the database
and when I came in I was explicitly told not to change that setup for
any reason.

So here's my problem. We also have a sample_date field (in the form
1/1/1111) which, you guessed it, contains the sample date which should
be the same as the last few digits of the Field_Sample_ID. Now for the
past few months the person who has been doing data entry forgot to
enter in the date into the sample_date field yet did remember to add
it to the Field_Sample_ID field. I need to develop a query that will
take those last few digits that correspond to the date and copy and
move it to the sample_date field (which has a default value of
1/1/1950.) There's also a date_entered field for the date the entry
was entered which will be useful for narrowing down the dates I need
to fix. I have no clue even where to really begin. I'm good at basic
queries, but I'm still in college and I'm just an intern here, yet I'm
the only person here who has any real education in computer science.

I have a few ideas like parsing through to the second "_" then reading
in the date by parsing it via the "." and then just reading the last 4
digits for the year. Would this be the correct way of looking at it?
Is there an easier way? Can anyone help out a little bit with the SQL
code for this?

Thanks in advance,

Mike

 

[mlieberman]

I resolved my issue. I just created a VBA function that parses through
the string looking for _ and . as delimiters.

 

[Marshall Barton]

I'm currently the database administrator for a relatively small (less
than 100,000 entries) database. Currently in our field_samples table
we have our unique key a Field_Sample_ID field which consists of a
three letter company code and underscore then a sampling place ID
which is variable in length and alphanumeric followed by an underscore
then a date which is in the form "1.1.1111". So for example a
Field_Sample_ID would look like "SAM_MW-43A_12.5.1982" or in cases
where there's multiple samples from the same place from the same time
"SAM_MW-43A_12.5.1982A". Yes I realize that the setup is terrible form
and makes no sense, but I was not the one who designed the database
and when I came in I was explicitly told not to change that setup for
any reason.

So here's my problem. We also have a sample_date field (in the form
1/1/1111) which, you guessed it, contains the sample date which should
be the same as the last few digits of the Field_Sample_ID. Now for the
past few months the person who has been doing data entry forgot to
enter in the date into the sample_date field yet did remember to add
it to the Field_Sample_ID field. I need to develop a query that will
take those last few digits that correspond to the date and copy and
move it to the sample_date field (which has a default value of
1/1/1950.) There's also a date_entered field for the date the entry
was entered which will be useful for narrowing down the dates I need
to fix. I have no clue even where to really begin. I'm good at basic
queries, but I'm still in college and I'm just an intern here, yet I'm
the only person here who has any real education in computer science.

You can extract the date part of that glop with an
expression:

UPDATE table
SET sample_date = IIf(Right(Mid(Field_Sample_ID,
InStrRev(Field_Sample_ID, "_") + 1), 1) Like "#",
Mid(Field_Sample_ID, InStrRev(Field_Sample_ID, "_") + 1),
Left(Mid(Field_Sample_ID, InStrRev(Field_Sample_ID, "_") +
1), Len(Mid(Field_Sample_ID, InStrRev(Field_Sample_ID,
"_") + 1)) - 1))
WHERE sample_date = #1/1/1950#

But I would create a UDF to do the work:

Public Function GetSDate(FS As Variant)
Dim sd As String

If IsNull(FS) Then
GetSDate = Null
Else
sd = Mid(FS, InStrRev(FS, "_") + 1)
If Not Right(sd, 1) Like "#" Then
sd = Left(sd, Len(sd) - 1)
End If
GetSDate = CDate(Replace(sd, ".", "/"))
End If
End Function

Then the update would look like:

UPDATE table
SET sample_date = GetSDate(Field_Sample_ID)
WHERE sample_date = #1/1/1950#