Form or Subform (or query)?

[Guest]

I have imported files from an excel spreedsheet into a new database in an
attempt merge some fields. In previous years this data was provided to me
with patient data being on "one" row with certain fields that have different
dates being denoted with the test perfomed and the letter of the month beside
it ex: hgbo for hemoglobin october. This year the contractor split the data
into "three" different rows to accomodate the three different months of tests
instead of the one row with three columns. How can I get this in the form I
need it? Do I create a subform or write a query? Also, I realize I will
have to create the column headings "hgbo, hgbn, hgbd" but I don't know how to
tell acess to take the second and third instance of the "idnum" and place
values in hgbn and hgbd fields. There are some 7k records in this file.
please help

 

[Kc-Mass]

Tony
Copy your table structure to a new table, add your new fields and run
something of this nature (Air code).

Sub Unstack()
Dim db As Database
Dim RSOld As Recordset
Dim RSNew As Recordset
Dim strMarker As String
Set db = CurrentDb
Set RSOld = db.OpenRecordset("tblOld", dbOpenSnapshot)
Set RSNew = db.OpenRecordset("tblNew", dbOpenDynaset)
RSOld.MoveFirst
Do While Not RSOld.EOF
strMarker = RSOld!IDnum
RSNew.AddNew
Do While RSOld!IDnum = strMarker And Not RSOld.EOF
If RSOld!hgb Is October Then
RSNew!NewFld1 = RSOld!hgb
ElseIf RSOld!hgb Is November Then
RSNew!NewFld2 = RSOld!hgb
Else
RSNew!NewFld3 = RSOld!hgb
End If
RSNew.Update
RSOld.MoveNext
Loop
Loop

Kevin C