Retrieving Entries Made on Separate Days

[avilliers]

I need some assistance here as I'm having trouble visualizing what I
need to do:

I have patients taking Form A of a test on one day and then some time
later the patient will take Form B of the test. There is a separate
score for each Form A and each Form B and then a combined score for
both forms for the same patient. If patients take more than one Form A/
B set of test the data has to be paired with the latest iteration of
testing to get the combined score.

What I'm having heartburn over is how to get both forms data back
together to generate the combined score.

Each patient has an unique identifier.
There is a date associated with each Form A and B. The dates will
never be equal or same day.

I'm thinking three tables: one for Form A results; one for Form B; and
lastly one that stores the combined results.

Of course there are the usual reports; Form A, Form B and the combined
scores.

I'm stumped on how to link the Form A and B together for each patient.

Ideas appreciated.

-av-

 

[KARL DEWEY]

Do not use separate table for each form.
First have a table for patient like this ---
PatientID - autonumber - primary key
LName - text
FName -
MI -
Sex -
DOB - DateTime
etc. --- address, phone NOK, emergency contact, ...

Patient_Visit ---
VisitID - autonumber - primary key
PatientID - number - long integer - foreign key
VisitDate - DateTime
Purpose -
Test -
Score –

Use these queries (Subqueries would be easier) --
Patient_Last_Visit_Test ---
SELECT Patient_Visit.PatientID, Max(Patient_Visit.VisitDate) AS
MaxOfVisitDate, Patient_Visit.Test
FROM Patient_Visit
GROUP BY Patient_Visit.PatientID, Patient_Visit.Test;

Patient_Last_Visit_Score ---
SELECT Patient.PatientID, Patient.LName, Patient.FName, Patient.MI,
Patient_Last_Visit_Test.Test, Patient_Visit.VisitDate, Patient_Visit.Score
FROM (Patient INNER JOIN Patient_Last_Visit_Test ON Patient.PatientID =
Patient_Last_Visit_Test.PatientID) INNER JOIN Patient_Visit ON
(Patient_Last_Visit_Test.Test = Patient_Visit.Test) AND
(Patient_Last_Visit_Test.MaxOfVisitDate = Patient_Visit.VisitDate) AND
(Patient_Last_Visit_Test.PatientID = Patient_Visit.PatientID);

Patient_Last_Visit_Final_Score --
SELECT Patient_Last_Visit_Score.LName, Patient_Last_Visit_Score.FName,
Patient_Last_Visit_Score.MI, Max(IIf([Patient_Last_Visit_Score].[Test]="Form
A",[Patient_Last_Visit_Score].[Score],0)+IIf([Patient_Last_Visit_Score_1].[Test]="Form B",[Patient_Last_Visit_Score_1].[Score],0)) AS [Combined Score]
FROM Patient_Last_Visit_Score INNER JOIN Patient_Last_Visit_Score AS
Patient_Last_Visit_Score_1 ON Patient_Last_Visit_Score.PatientID =
Patient_Last_Visit_Score_1.PatientID
GROUP BY Patient_Last_Visit_Score.LName, Patient_Last_Visit_Score.FName,
Patient_Last_Visit_Score.MI;