Export and converge data

[Wissam]

Hi,
That following is an example of what I am trying to do:

I have an access table (SourceTable) with the following 3 records (2 of them
are for the same patient):
Patient# Date1 Result1 Date2 Result2
1 12/1/2007 100
1 12/3/2007 120
2 1/1/2008 80

I want a code that would transfer the data to another table (TargetTable) as:
Patient# Date1 Result1 Date2 Result2
1 12/1/2007 100 12/3/2007 120
2 1/1/2008 80


Thanks a lot for the help.

 

[John Spencer]

IF you have a max of two records per patient number, the following MIGHT
work for you

SELECT A.[Patient#]
, Max(A.Date1) as ADate
, Max(A.Result1) as AResult
, Max(B.Date2) as BDate
, Max(B.Result2) as BResult
FROM SourceTable as A LEFT JOIN SourceTable as B
ON A.[Patient#] = B.[Patient#]
GROUP BY A.[Patient#]

In Design view
-- Add your table to the query twice
-- Drag from Patient# to Patient#
-- Double click on the join line and select All from one copy and Any for
the other copy of the table
-- Select View: Totals from the menu
-- Add Patient# from one table and Date1 and Result1 from the table
--Add Date2 and Result2 from the other table
-- Change GROUP BY to MAX under the Date and Result fields

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Wissam]

Hi John,
Thanks a lot for your help.
Actually, per each patient, I have about Date1--->Date40,Result1-->Result40,
and many other fields with field1-->Field40 per patients. What I did below
worked well for me. Is there an easy way to loop around the fields; what I
want is a code for something like:
For N =1 to 40
If rsSource![ResultN] <> "" Then
rsTarget.Edit
rsTarget![DateN] = rsSource![DateN]
rsTarget![ResultN] = rsSource![ResultN]
rsTarget.Update
End If

Thanks in advance.
*************************************************
'I constructed table called TargetTable with the fields I wanted and match
those in the SourceTable:
Patient#,Date1,Result1,Date2,Result2,..,Date40,Result40. I made Patient# as a
primary key in the table.
'I put the following Code behind the command button I want.

Dim db As DAO.Database
Dim rsSource As DAO.Recordset
Dim rsTarget As DAO.Recordset
Dim lngcount as lnteger

Set db = CurrentDb()
Set rsSource = db.OpenRecordset("SourceTable")
Set rsTarget = db.OpenRecordset("TargetTable")
rsSource.MoveFirst
lngcount = rsTarget.RecordCount
If lngcount = 0 Then
rsTarget.AddNew
rsTarget![Patient#] = rsSource![Patient#]
rsTarget.Update
End If
Do Until rsSource.EOF
'Search for matching record based on primary key field which is Patient#
rsTarget.Index = "PrimaryKey"
rsTarget.Seek "=", rsSource![Patient#]
If rsTarget.NoMatch Then ' .NoMatch is FALSE if Patient# found
rsTarget.AddNew
rsTarget![Patient#] = rsSource![Patient#]
rsTarget.Update
End If
rsTarget.Index = "PrimaryKey"
rsTarget.Seek "=", rsSource![Patient#]
If rsSource![Result1] <> "" Then
rsTarget.Edit
rsTarget![Date1] = rsSource![Date1]
rsTarget![Result1] = rsSource![Result1]
rsTarget.Update
End If
If rsSource![Result2] <> "" Then
rsTarget.Edit
rsTarget![Date2] = rsSource![Date2]
rsTarget![Result2] = rsSource![Result2]
rsTarget.Update
End If
rsSource.MoveNext
Loop