Please help with deleting records

[Guest]

I have a table which has over 250,000 records I have the following situation:

RecordID PatientID MedType DoseDate Frequency Dose
1 004444 A10 iv 7/12/96 6 150
2 004444 A10 iv 7/17/96 6 150
3 004444 A10 iv 9/30/96 6 150
4 004444 A10 iv 10/1/96 6 100
I need to do the following:
if Frequency and Dose are the same per patient, I want to delet these
records of the higher date and leave only the one with the lowest date. In
the example above, RecordID 2 and 3 should be deleted because Freq. and Dose
are the same as 1 but not 1 or 4. the process needs to be automated there is
a lot of records to be done manually. I have used subqueries but they took
forever.
any idea please,
thanks
Al

 

[John Spencer]

Since you didn't post what you have tried, you may end up getting the same
solution you have already developed

One way to do this might be to create a new identical table with no records and
then import the records you want into that table. It would be faster.

The query you would use to select the records would look something like this
one. You should be able to turn that into an append (or insert query).

SELECT Distinct YT2.*
FROM YourTable as YT2 INNER JOIN
( SELECT PatientID, MedType, Frequency, Dose
, Min(YT.DoseDate) as DoseDate
FROM YourTable as YT
GROUP BY PatientID, MedType, Frequency, Dose ) as YT3
ON YT2.PatientID = YT3.PatientID
AND YT2.MedType = YT3.MedType
AND YT2.Frequency = YT3.Frequency
AND YT2.Dose = YT3.Dose
AND YT2.DoseDate = YT3.DoseDate

If you have to delete records from the current table, you should be able to use
something like

DELETE DistinctRow YT2.*
FROM YourTable as YT2 LEFT JOIN
( SELECT PatientID, MedType, Frequency, Dose
, Min(YT.DoseDate) as DoseDate
FROM YourTable as YT
GROUP BY PatientID, MedType, Frequency, Dose) as YT3
ON YT2.PatientID = YT3.PatientID
AND YT2.MedType = YT3.MedType
AND YT2.Frequency = YT3.Frequency
AND YT2.Dose = YT3.Dose
AND YT2.DoseDate = YT3.DoseDate
WHERE YT3.PatientID is Null

Whatever you do, I suggest you test on a copy of your data to see if it works
correctly. The delete solution above won't get rid of exact duplicates

 

[Guest]

when I ran the first qry I got an error message "Invalid use of '.','!','()'
in query expression. It does not like the '.' at the last section
**********************
ON YT2.PatientID = YT3.PatientID
AND YT2.MedType = YT3.MedType
AND YT2.Frequency = YT3.Frequency
AND YT2.Dose = YT3.Dose
AND YT2.DoseDate = YT3.DoseDate
****************************
your query which I used :
*********************************
SELECT Distinct YT2.*
FROM YourTable as YT2 INNER JOIN
( SELECT PatientID, MedType, Frequency, Dose
, Min(YT.DoseDate) as DoseDate
FROM YourTable as YT
GROUP BY PatientID, MedType, Frequency, Dose ) as YT3
ON YT2.PatientID = YT3.PatientID
AND YT2.MedType = YT3.MedType
AND YT2.Frequency = YT3.Frequency
AND YT2.Dose = YT3.Dose
AND YT2.DoseDate = YT3.DoseDate
***********************************
The subqueries I used before were just to get me the next date and the
previous one it was something like:
(Select Min(DoseDate) As NextDate From tblMedication as tblMed where
tblMed.PatientID=tblMedication.PatientID and
tblMed.Dosedate>tblMedication.DoseDate)
This way I would have Previous date, current date, next date along with the
other data on one row. I wanted to go down this road to explore the
possiblity of using "iif" to compare dates and dose,frequency values to keep
or eleminate records.

I do have a new table for Medical records and the reason why I am doing this
is that the clinic, that I am working for, had been capturing the data in a
wrong way and I need to clean the data in the current table and move it to
the new. I do not have to literally to delete the records, I can filter them
out. The point is that I do not want to transfer them to the new table.
thanks John
Al

 

[John Spencer]

Are you saying that the following query fails with a syntax error? I'm
sorry but I do not see what could cause that problem. Sometimes when you
cut and paste from the newsgroup windows, extraneous (invisible) characters
get introduced. Try typing in the query and see if that works.

SELECT YT2.*
FROM tblMedication as YT2 INNER JOIN

( SELECT PatientID, MedType, Frequency, Dose
, Min(YT.DoseDate) as DoseDate
FROM tblMedication as YT
GROUP BY PatientID, MedType, Frequency, Dose ) as YT3

ON YT2.PatientID = YT3.PatientID
AND YT2.MedType = YT3.MedType
AND YT2.Frequency = YT3.Frequency
AND YT2.Dose = YT3.Dose
AND YT2.DoseDate = YT3.DoseDate

You can do this with two queries.

Query One - saved as q_MinDate
Create an aggregate query on PatientID, MedType, Frequency, Dose and
DoseDate.
Group by the first four fields and get the minimum of DoseDate
Save the query

Create a new query with tblMedication and q_minDate.
Create a join between the 5 fields. Drag from PatientID to PatientId, etc.

Run the query and see if it gives you the results you want.

 

[Guest]

Yes it did, thank you
Al

Are you saying that the following query fails with a syntax error? I'm
sorry but I do not see what could cause that problem. Sometimes when you
cut and paste from the newsgroup windows, extraneous (invisible) characters
get introduced. Try typing in the query and see if that works.

SELECT YT2.*
FROM tblMedication as YT2 INNER JOIN

( SELECT PatientID, MedType, Frequency, Dose
, Min(YT.DoseDate) as DoseDate
FROM tblMedication as YT
GROUP BY PatientID, MedType, Frequency, Dose ) as YT3

ON YT2.PatientID = YT3.PatientID
AND YT2.MedType = YT3.MedType
AND YT2.Frequency = YT3.Frequency
AND YT2.Dose = YT3.Dose
AND YT2.DoseDate = YT3.DoseDate

You can do this with two queries.

Query One - saved as q_MinDate
Create an aggregate query on PatientID, MedType, Frequency, Dose and
DoseDate.
Group by the first four fields and get the minimum of DoseDate
Save the query

Create a new query with tblMedication and q_minDate.
Create a join between the 5 fields. Drag from PatientID to PatientId, etc.

Run the query and see if it gives you the results you want.