Sandy,
I hear what you're saying, however...
<rant>
....that's no excuse for poor database design. As for your statement that
this thing will only be used for a few months and then scrapped...... I
can't count the number of times I've heard that statement, only to return a
year or more later to find it has become a mission-critical system. Even if
it was only to be temporary, that's no excuse for designing it badly. The
problem with what you're doing is that it is what we call, "committing
Excel" - trying to do in Access what you would do in Excel. Access is a
relational database, and thus, you should always let the database do what it
does best. Let the database do the work for you.
All this stuff could more than likely have been stored in the same table. If
you want to extract relational data based on date, then it doesn't matter
(to you) how that data is stored, as long as you can get at it with as
little fuss as possible.
</rant>
OK, here goes. You didn't give me any table or field names, so I'll just
make them up and you can rename them as you need. In the following example
code, tblSource is the table that contains that long concatenation of dates.
BigDate is the field in that table that actually contains the data. The
table that contains 3/04, 11/03, 11/02, etc, I'll call tblDest, and (though
I despise this naming strategy) I'll retain your field names of 3/04, 11/03,
11/02, etc.
Now before you do anything, rename all those 3/04, 11/03, 11/02 columns so
they are ALL 5-digit names. What I mean is, instead of being 3/04, make sure
they're 03/04. I haven't tested this, but it should work OK. If you have any
problems, or if I haven't provided what you were after, just let me know.
Public Sub ParseData()
Dim ws As WorkSpace
Dim db As Database
Dim rsSrc As DAO.Recordset
Dim rsDest As DAO.Recordset
Dim iCtr As Integer
Dim sFldName As String
On Error Goto Proc_Err
Set ws = DbEngine(0)
Set db = CurrentDb
Set rsDest = db.OpenRecordset("tblDest", dbOpenDynaset)
Set rsSrc = db.OpenRecordset("SELECT BigDate FROM tblSource",
dbOpenSnapshot)
ws.BeginTrans
rsDest.Edit
Do While Not rsSrc.EOF
For iCtr = 1 To Len(rsSrc!BigDate) Step 4
sFldName = Mid(rsSrc!BigDate, iCtr, 4)
sFldName = Left(sFldName, 2) & "\" & Right(sFldName, 2)
rsDest(sFldName) = True
Next iCtr
rsSrc.MoveNext
Loop
rsDest.Update
ws.CommitTrans
Proc_Exit:
rsSrc.Close
rsDest.Close
ws.Close
Set rsSrc = Nothing
Set rsDest = Nothing
Set db = Nothing
Set ws = Nothing
Exit Sub
Proc_Err:
rsDest.CancelUpdate
ws.Rollback
MsgBox Err.Number & vbCrLf & Err.Description
Resume Proc_Exit
End Sub
Is this what you wanted?
Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
Sandy said:
Graham:
Thanks for your response. I totally agree with you, however this is a
situation where there are multiple tables that certain material has to be
extracted from for the purpose of analysis and is to be used for a few
months and then scrapped. In other words, "No need to make it perfect," I
was told.
The information I am trying to get out of this is whether they ordered on
11/98 and 11/99 and 11/00 and, etc. or if they only ordered on 11/01 and
11/97, for example. I can't see a way of doing this other than creating a
field for each date and using a boolean 1 for yes and perhaps a 2 for no.
The other tables in the DB are already in the "1, 2" format; thus, to
quickly obtain the info, in this case normalization does not necessarily
make sense. I know . . . I cringe at it too!
Let me know if you have any suggestions.
Sandy
----- Graham R Seach wrote: -----
Sandy,
Sure it's possible, but I have to question why you're creating separate
fields for each date. This does not sit well with standard database
normalisation rules. What are you ultimately trying to do?
Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
Sandy said:
field.
Below is what I have to work with:
040303110211020901110109001100090003991198119809
[The rest of the table consists of about 30 other unrelated fields]
(This should really be split as follows:
0403 0311 0211 0209 0111 0109 0011 009 0003 9911 9811
9809
(These would correspond to):
3/04 11/03 11/02 9/02 11/01 9/01 11/00 9/00 3/00 11/99 11/98 9/98
WHAT A MESS!!!!)
At any rate, I created fields for 3/04, 11/03, 11/02, etc. I need
code
that goes through each row and when it gets to this field, if there is a
value for 0403, for example, it would put a "1" in the 3/04 column for that
row; then if it has 0311, it would put a "1" in the 11/03 column and if it
does not have a value for any of the above dates, it skips to the next until
all the values are represented by a "1" in the corresponding field, if they
have a numerical value for same and are null if they don't. (Other rows
have other values for this field, e.g. they may have something like
0211011101090011000900039911)
