Please help with duplicates

[Guest]

PatientID Exam Date Med Dose Freq BodyWt Concentrtn
DC/OFF
004444 5/8/1996 A10 iv 87
004444 5/8/1996 A10 iv 10 6 87 0.3

004444 5/9/1996 A10 iv 89
004444 5/9/1996 A10 iv 20 6 89 0.3
004444 5/10/1996 A10 iv 89
004444 5/10/1996 A10 iv 30 6 89 0.3
004444 5/11/1996 A10 iv 90
004444 5/11/1996 A10 iv 40 6 90 0.3
004444 5/12/1996 A10 iv 90
004444 5/12/1996 A10 iv 50 6 90 0.3
004444 5/13/1996 A10 iv 90
004444 5/13/1996 A10 iv 60 6 90 0.3
004444 5/14/1996 A10 iv 80
004444 5/14/1996 A10 iv 70 6 80 0.3
004444 5/15/1996 A10 iv
004444 5/16/1996 A10 iv
OFF

The above data is the data I have in a query. I want to delete the records
that have duplicate dates but they are blank (except for Bodywt field) from
After field "Med". In the above exampl I would like to delete the first
5/8/1996 because no data exist except for the Bodywt. Any idea how to do
that. another important condition, is that if the row has OFF or DC under
OFF/DC then it should not be deleted. So if 5/16/1996 was a duplicate record,
I would have to keep the one that has OFF.
thanks
Al

 

[Smartin]

PatientID Exam Date Med Dose Freq BodyWt Concentrtn
DC/OFF
004444 5/8/1996 A10 iv 87
004444 5/8/1996 A10 iv 10 6 87 0.3

004444 5/9/1996 A10 iv 89
004444 5/9/1996 A10 iv 20 6 89 0.3
004444 5/10/1996 A10 iv 89
004444 5/10/1996 A10 iv 30 6 89 0.3
004444 5/11/1996 A10 iv 90
004444 5/11/1996 A10 iv 40 6 90 0.3
004444 5/12/1996 A10 iv 90
004444 5/12/1996 A10 iv 50 6 90 0.3
004444 5/13/1996 A10 iv 90
004444 5/13/1996 A10 iv 60 6 90 0.3
004444 5/14/1996 A10 iv 80
004444 5/14/1996 A10 iv 70 6 80 0.3
004444 5/15/1996 A10 iv
004444 5/16/1996 A10 iv
OFF

The above data is the data I have in a query. I want to delete the records
that have duplicate dates but they are blank (except for Bodywt field) from
After field "Med". In the above exampl I would like to delete the first
5/8/1996 because no data exist except for the Bodywt. Any idea how to do
that. another important condition, is that if the row has OFF or DC under
OFF/DC then it should not be deleted. So if 5/16/1996 was a duplicate record,
I would have to keep the one that has OFF.
thanks
Al

If you are getting the results above from a query just add a couple
conditions to the WHERE clause:

WHERE
([DC/OFF] IN ('DC','OFF')) OR
DOSE IS NOT NULL

Make sure you set off the 1st condition (with the OR) with precedence
over all others using parenthesis.

This assumes a blank Dose is a reliable indicator that the rest of the
data to the right is also blank (except DC/OFF).