Selectively deleting records

[johnds]

I am researching the use of physician services that are recorded in a
billing database of over 10 million records. I am only interested in
the patient's treatment type, and their attributes (age and sex).
Simple enough, but the DB is used to record journal entries and
adjustments. If a patient gets recoded, then that is entered as a
separate record with a negative unit. Typically there are three
records:
patient# treatment# units
123 200 1
123 200 -1
123 201 1
This is really only one correct visit for treatment 201. If I just
delete units <0, then I get an incorrect unit for 200, and one correct
unit for 201. Need to mark and delete the first to entries. I can do it
by creating a table totals for negative units by treatment and subtract
from the totals for each treatment total, but I then I have all these
records that have to be excluded for unit level analysis. Would rather
just mark the two records and delete. There can be many records for one
individual for different codes, so it gets complicated pretty fast.
thanks, really need to get this done

[Arno R]

Would a simple GroupBy and SUM help you?

You can exclude UnitSums that are 0 if that suits you.

Something like:
SELECT YourTable.Patie nt, YourTable.treat ment, Sum(YourTable.U nit) AS Unit
FROM YourTable
GROUP BY YourTable.Patie nt, YourTable.treat ment
HAVING (((Sum(YourTabl e.Unit))<>0));

If this gives the needed results than change into a maketablequery and report on the new table

Or only use the GroupBy and Sum without the exclusion.
The Sum gives you the real number of treatments.
Report on the treatments where the sum is not 0...

Arno R
"johnds" <jo*******@yaho o.comschreef in bericht news:11******** **************@ m79g2000cwm.goo glegroups.com.. .

>I am researching the use of physician services that are recorded in a
billing database of over 10 million records. I am only interested in
the patient's treatment type, and their attributes (age and sex).
Simple enough, but the DB is used to record journal entries and
adjustments. If a patient gets recoded, then that is entered as a
separate record with a negative unit. Typically there are three
records:
patient# treatment# units
123 200 1
123 200 -1
123 201 1
This is really only one correct visit for treatment 201. If I just
delete units <0, then I get an incorrect unit for 200, and one correct
unit for 201. Need to mark and delete the first to entries. I can do it
by creating a table totals for negative units by treatment and subtract
from the totals for each treatment total, but I then I have all these
records that have to be excluded for unit level analysis. Would rather
just mark the two records and delete. There can be many records for one
individual for different codes, so it gets complicated pretty fast.
thanks, really need to get this done

[johnds]

That makes perfect sense. I knew somewhere in the steps the individual
patient had to be grouped for each treatment, but had not thought of
the exclusion. I did not mention in my post that these entries are all
on the same day. But it should not matter if it happens in different
periods, as long as it is the same patient for the same treatment, the
total visits and units for the year will be correct regardless. I am
only looking at totals for the year. Also, your method will give me a
record that is one visit, so now I have number of visits by treatment,
and units provided.

thanks,
Arno R wrote:

Would a simple GroupBy and SUM help you?

You can exclude UnitSums that are 0 if that suits you.

Something like:
SELECT YourTable.Patie nt, YourTable.treat ment, Sum(YourTable.U nit) AS Unit
FROM YourTable
GROUP BY YourTable.Patie nt, YourTable.treat ment
HAVING (((Sum(YourTabl e.Unit))<>0));

If this gives the needed results than change into a maketablequery and report on the new table

Or only use the GroupBy and Sum without the exclusion.
The Sum gives you the real number of treatments.
Report on the treatments where the sum is not 0...

Arno R
"johnds" <jo*******@yaho o.comschreef in bericht news:11******** **************@ m79g2000cwm.goo glegroups.com.. .

I am researching the use of physician services that are recorded in a
billing database of over 10 million records. I am only interested in
the patient's treatment type, and their attributes (age and sex).
Simple enough, but the DB is used to record journal entries and
adjustments. If a patient gets recoded, then that is entered as a
separate record with a negative unit. Typically there are three
records:
patient# treatment# units
123 200 1
123 200 -1
123 201 1
This is really only one correct visit for treatment 201. If I just
delete units <0, then I get an incorrect unit for 200, and one correct
unit for 201. Need to mark and delete the first to entries. I can do it
by creating a table totals for negative units by treatment and subtract
from the totals for each treatment total, but I then I have all these
records that have to be excluded for unit level analysis. Would rather
just mark the two records and delete. There can be many records for one
individual for different codes, so it gets complicated pretty fast.
thanks, really need to get this done