Create a UNION query with the following SQL:
SELECT Diagnosis.VisitID, First(Diagnosis.DiagID) AS DiagID, 1 AS [Order]
FROM Diagnosis
GROUP BY Diagnosis.VisitID, 1
ORDER BY Diagnosis.VisitID, 1
UNION
SELECT Diagnosis.VisitID, Last(Diagnosis.DiagID) AS DiagID, 3 AS [Order]
FROM Diagnosis
GROUP BY Diagnosis.VisitID, 3;
and name it qryFirstAndLast
Create another UNION query with the following SQL:
SELECT qryFirstAndLast.VisitID, qryFirstAndLast.DiagID, Diagnosis.Diagnosis AS Diag, qryFirstAndLast.Order
FROM Diagnosis INNER JOIN qryFirstAndLast ON Diagnosis.DiagID = qryFirstAndLast.DiagID
ORDER BY qryFirstAndLast.VisitID, qryFirstAndLast.Order
UNION
SELECT Diagnosis.VisitID, Diagnosis.DiagID, Diagnosis.Diagnosis as Diag, 2 AS [Order]
FROM qryFirstAndLast RIGHT JOIN Diagnosis ON (qryFirstAndLast.VisitID = Diagnosis.VisitID) AND (qryFirstAndLast.DiagID = Diagnosis.DiagID)
WHERE (((qryFirstAndLast.DiagID) Is Null) AND ((qryFirstAndLast.VisitID) Is Null));
And name this query qryOrderedDiagnosis
This provides us with a Recordset of up to three records per visit in the order of diagnosis 1,2,or 3. We can now use this query to create a temporary table. Use the following SQL to create a new query:
SELECT qryOrderedDiagnosis.VisitID, qryOrderedDiagnosis.DiagID, qryOrderedDiagnosis.Diag, qryOrderedDiagnosis.Order INTO tblTempOrderedDiagnosis
FROM qryOrderedDiagnosis;
Name this query qryMTOrderedDiagnosis
We can now update the appropriate fields in table Visits with the Diagnosis data. Use the following SQL to create an ACTION query(UPDATE) that will post the diagnosis to the appropriate fields:
UPDATE Visits INNER JOIN tblTempOrderedDiagnosis ON Visits.VisitID = tblTempOrderedDiagnosis.VisitID SET Visits.[Primary] = NZ(Switch([tblTempOrderedDiagnosis]![Order]=1,[tblTempOrderedDiagnosis]![Diag]),[Visits]![Primary]), Visits.Secondary = NZ(Switch([tblTempOrderedDiagnosis]![Order]=2 And [tblTempOrderedDiagnosis]![Diag]<>[Visits]![Primary],[tblTempOrderedDiagnosis]![Diag],[tblTempOrderedDiagnosis]![Order]=3 And IsNull([Visits]![Secondary]) And [tblTempOrderedDiagnosis]![Diag]<>[Visits]![Primary],[tblTempOrderedDiagnosis]![Diag]),[Visits]![Secondary]), Visits.Third = NZ(Switch([tblTempOrderedDiagnosis]![Order]=3 And Not IsNull([Visits]![Secondary]),[tblTempOrderedDiagnosis]![Diag]),[Visits]![Third]);
Name this query qryUpdVisits
To execute this process use the following code:
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryMTFirstAndLast"
DoCmd.OpenQuery "qryUpdVisits"
DoCmd.SetWarnings True
This process will create a temporary table of Diagnosis and number their order 1,2,3. The last query will use that table to update the table Visits with the Diagnosis data. If there is only 1 diagnosis for a Visit then only the Primary is updated. If only 2 diagnosis per visit then only Primary and Secondary. If any of the diagnosis for a visit are duplicated it will be skipped.
Give this a try as with my text data it will give you exactly what you want. Bob Scriver
);"