Difficult Query Problem

[music_al]

OK, this might be tricky to explain but I'll try.

I have a Form which has a Sub Form and that Sub Form has 2 Sub Forms.

frm_Patient
---frm_Appointment
------frm_Blood_Pressure
------frm_Pulse_Rate

A patient can have many appointments. An appointment does not have to have a Blood Pressure record or a Pulse rate record related to it. For each Blood Pressure and Pulse Rate record, I have a check box which is checked for the "optimum result". The data in the tables might look like that shown below.

Patient1
---Appointment1
------Blood_Pressure1
------Blood_Pressure2 x
------Blood_Pressure3
------Pulse_Rate1 x
------Pulse_Rate2

Patient2
---Appointment2
------Blood_Pressure4
------(no Pulse_Rate records)

Patient3
---Appointment3
------(no Blood_Pressure records)
------Pulse_Rate3

Patient4
---Appointment4
------(no Blood_Pressure records)
------(no Pulse_Rate records)

The query on the data above is returning 9 records as I would expect.

Appointment1 Blood_Pressure1 Pulse_Rate1
Appointment1 Blood_Pressure1 Pulse_Rate2
Appointment1 Blood_Pressure2 Pulse_Rate1
Appointment1 Blood_Pressure2 Pulse_Rate2
Appointment1 Blood_Pressure3 Pulse_Rate1
Appointment1 Blood_Pressure3 Pulse_Rate2
Appointment2 Blood_Pressure4 (no data)
Appointment3 (no data) Pulse_Rate3
Appointment4 (no data) (no data)

but, the problem is, I only want the query to return MOST RECENT appointment for each patient and only the red records of each appointment.

I have tried putting a "-1" in the criteria in the Optimum_Result" field, but this then disregards the other records.

I really am stuck. Can anyone help ???

Thanks


Al

 

[georgedwilkinson]

And what data field do you have that indicates which record is the most recent?

It'd help if you included the text of your query, too.

 

[gemma-the-husky]

so
where is the date stored?
and what determines a red item?

 

[music_al]

The date is stored in the Appointment Table

Where I have MANY Blood Pressure and Pulse Rate results, the user would select which Blood Pressure result is the optimum result and which Pulse Rate result is the optimum result.

Where I have ONLY ONE Blood Pressure result or Pulse Rate result, this would be the optimim result.

 

[gemma-the-husky]

probably moving towards a slightly complex union query to

a) select the flagged optimum results
b) select the single results
c) union them together

so

how do you make sure that in a multi-appointment patient there is one and only one selected optimum result?

what if there are two+ marked as optimum?

what if there are none marked as optimum?

 

[music_al]

how do you make sure that in a multi-appointment patient there is one and only one selected optimum result?

Well, that will be down to te users t make sure they only select one

what if there are two+ marked as optimum?

I would hope they wouldnt. I tried to think of a way of doing this, maybe by using a FindDuplicates query which would perform an UNDO if a second checkbox was selected. I'll work on that...

what if there are none marked as optimum?

If only one Blood Pressure or Pulse Rate record was present, that record WOULD BE the optimum record.

Thanks

Al