This is another question about eliminating accounting entries in a
clinical database, yet retaining the valid record. Latest wrinkle is
being able to sum the visits to the doctor, and the units of services
received. Here's the problem: If a visit is wrongly coded, then a
reverse entry is made as follows.
PTID CODE UNITS
1 100 2
1 100 -2
1 110 2

This is actually one visit for code 110 and the patient received 2
units of service. Thanks to a member's advice, I used:

SELECT ptid, code, sum(units) AS unit
FROM PTDB
GROUP BY ptid, code
HAVING (((sum(units))<>0));

This gives me a correct unit of service (I checked manually), but I
still need a field with a count of 1 for the visit. Most reports focus
on the number of visits taken, not how long much time you spent in the
office. Is there a way to get both sum of units and count of visits?
This is pretty basic stuff to most people, but it's killing me. Thks.

johngl wrote:
> This is another question about eliminating accounting entries in a
> clinical database, yet retaining the valid record. Latest wrinkle is
> being able to sum the visits to the doctor, and the units of services
> received. Here's the problem: If a visit is wrongly coded, then a
> reverse entry is made as follows.
> PTID CODE UNITS
> 1 100 2
> 1 100 -2
> 1 110 2
>
> This is actually one visit for code 110 and the patient received 2
> units of service. Thanks to a member's advice, I used:
>
> SELECT ptid, code, sum(units) AS unit
> FROM PTDB
> GROUP BY ptid, code
> HAVING (((sum(units))<>0));
>
> This gives me a correct unit of service (I checked manually), but I
> still need a field with a count of 1 for the visit. Most reports focus
> on the number of visits taken, not how long much time you spent in the
> office. Is there a way to get both sum of units and count of visits?
> This is pretty basic stuff to most people, but it's killing me. Thks.
>

Air SQL:

SELECT sum(1) AS visits, ptid, code, sum(units) AS unit FROM PTDB GROUP
BY ptid, code HAVING (((sum(units))<>0));

Only records that have not been cancelled will show up so the number of
visits should be the same as the number of records returned by the query.

James A. Fortune
(E-Mail Removed)

Wisconsin produces more cranberries than any other state. --
http://www.wiscran.org/

I just ran your code, here is an actual set of 6 records, same patient:
PTID CODE UNITS
1 100 1
1 100 1
1 100 -1
1 100 1
1 100 -1
1 100 1

These are all the same code, if it works on one, it will work on all
codes. The correct result is 2 units and 2 visits, the bottom four are
a wash, leaving the top two. Your code produced 6 visits and 2 units. I
would be happy just to get the visits. Not sure why your code did not
work, it looks correct.

Any suggestions? Thanks again.

James A. Fortune wrote:
> johngl wrote:
> > This is another question about eliminating accounting entries in a
> > clinical database, yet retaining the valid record. Latest wrinkle is
> > being able to sum the visits to the doctor, and the units of services
> > received. Here's the problem: If a visit is wrongly coded, then a
> > reverse entry is made as follows.
> > PTID CODE UNITS
> > 1 100 2
> > 1 100 -2
> > 1 110 2
> >
> > This is actually one visit for code 110 and the patient received 2
> > units of service. Thanks to a member's advice, I used:
> >
> > SELECT ptid, code, sum(units) AS unit
> > FROM PTDB
> > GROUP BY ptid, code
> > HAVING (((sum(units))<>0));
> >
> > This gives me a correct unit of service (I checked manually), but I
> > still need a field with a count of 1 for the visit. Most reports focus
> > on the number of visits taken, not how long much time you spent in the
> > office. Is there a way to get both sum of units and count of visits?
> > This is pretty basic stuff to most people, but it's killing me. Thks.
> >
>
> Air SQL:
>
> SELECT sum(1) AS visits, ptid, code, sum(units) AS unit FROM PTDB GROUP
> BY ptid, code HAVING (((sum(units))<>0));
>
> Only records that have not been cancelled will show up so the number of
> visits should be the same as the number of records returned by the query.
>
> James A. Fortune
> (E-Mail Removed)
>
> Wisconsin produces more cranberries than any other state. --
> http://www.wiscran.org/

I just realized the problem. The sum of units is greater than zero, so
all are counted, problem is four of the records have to be deleted. I
should have added that there is a date field and all the adjustments
are made on the same day. I added this below for reference. Even if I
group by date, I still get a positive value for August, and the 3
visits for that day when in fact there is only one (why they do this is
beyond me).

johngl wrote:
> I just ran your code, here is an actual set of 6 records, same patient:
> PTID CODE UNITS DATE
> 1 100 1 Aug 20
> 1 100 1 Aug 20
> 1 100 -1 Aug 20
> 1 100 1 Sept 9
> 1 100 -1 Sept 9
> 1 100 1 Sept 9
>
> These are all the same code, if it works on one, it will work on all
> codes. The correct result is 2 units and 2 visits, the bottom four are
> a wash, leaving the top two. Your code produced 6 visits and 2 units. I
> would be happy just to get the visits. Not sure why your code did not
> work, it looks correct.
>
> Any suggestions? Thanks again.
>
> James A. Fortune wrote:
> > johngl wrote:
> > > This is another question about eliminating accounting entries in a
> > > clinical database, yet retaining the valid record. Latest wrinkle is
> > > being able to sum the visits to the doctor, and the units of services
> > > received. Here's the problem: If a visit is wrongly coded, then a
> > > reverse entry is made as follows.
> > > PTID CODE UNITS
> > > 1 100 2
> > > 1 100 -2
> > > 1 110 2
> > >
> > > This is actually one visit for code 110 and the patient received 2
> > > units of service. Thanks to a member's advice, I used:
> > >
> > > SELECT ptid, code, sum(units) AS unit
> > > FROM PTDB
> > > GROUP BY ptid, code
> > > HAVING (((sum(units))<>0));
> > >
> > > This gives me a correct unit of service (I checked manually), but I
> > > still need a field with a count of 1 for the visit. Most reports focus
> > > on the number of visits taken, not how long much time you spent in the
> > > office. Is there a way to get both sum of units and count of visits?
> > > This is pretty basic stuff to most people, but it's killing me. Thks.
> > >
> >
> > Air SQL:
> >
> > SELECT sum(1) AS visits, ptid, code, sum(units) AS unit FROM PTDB GROUP
> > BY ptid, code HAVING (((sum(units))<>0));
> >
> > Only records that have not been cancelled will show up so the number of
> > visits should be the same as the number of records returned by the query.
> >
> > James A. Fortune
> > (E-Mail Removed)
> >
> > Wisconsin produces more cranberries than any other state. --
> > http://www.wiscran.org/

johngl wrote:
> I just realized the problem. The sum of units is greater than zero, so
> all are counted, problem is four of the records have to be deleted. I
> should have added that there is a date field and all the adjustments
> are made on the same day. I added this below for reference. Even if I
> group by date, I still get a positive value for August, and the 3
> visits for that day when in fact there is only one (why they do this is
> beyond me).
>
> johngl wrote:
>
>>I just ran your code, here is an actual set of 6 records, same patient:
>>PTID CODE UNITS DATE
>>1 100 1 Aug 20
>>1 100 1 Aug 20
>>1 100 -1 Aug 20
>>1 100 1 Sept 9
>>1 100 -1 Sept 9
>>1 100 1 Sept 9
>>
>>These are all the same code, if it works on one, it will work on all
>>codes. The correct result is 2 units and 2 visits, the bottom four are
>>a wash, leaving the top two. Your code produced 6 visits and 2 units. I
>>would be happy just to get the visits. Not sure why your code did not
>>work, it looks correct.
>>
>>Any suggestions? Thanks again.
>>
>>James A. Fortune wrote:
>>
>>>johngl wrote:
>>>
>>>>This is another question about eliminating accounting entries in a
>>>>clinical database, yet retaining the valid record. Latest wrinkle is
>>>>being able to sum the visits to the doctor, and the units of services
>>>>received. Here's the problem: If a visit is wrongly coded, then a
>>>>reverse entry is made as follows.
>>>>PTID CODE UNITS
>>>>1 100 2
>>>>1 100 -2
>>>>1 110 2
>>>>
>>>>This is actually one visit for code 110 and the patient received 2
>>>>units of service. Thanks to a member's advice, I used:
>>>>
>>>>SELECT ptid, code, sum(units) AS unit
>>>>FROM PTDB
>>>>GROUP BY ptid, code
>>>>HAVING (((sum(units))<>0));
>>>>
>>>>This gives me a correct unit of service (I checked manually), but I
>>>>still need a field with a count of 1 for the visit. Most reports focus
>>>>on the number of visits taken, not how long much time you spent in the
>>>>office. Is there a way to get both sum of units and count of visits?
>>>>This is pretty basic stuff to most people, but it's killing me. Thks.
>>>>
>>>
>>>Air SQL:
>>>
>>>SELECT sum(1) AS visits, ptid, code, sum(units) AS unit FROM PTDB GROUP
>>>BY ptid, code HAVING (((sum(units))<>0));
>>>
>>>Only records that have not been cancelled will show up so the number of
>>>visits should be the same as the number of records returned by the query.
>>>
>>>James A. Fortune
>>>(E-Mail Removed)
>>>
>>>Wisconsin produces more cranberries than any other state. --
>>>http://www.wiscran.org/
>
>

I see that I made a poor guess about what your data looks like because
of the small sample. I'll try to take a look at it again tomorrow night.

James A. Fortune
(E-Mail Removed)

Thanks, really appreciate it. The file has over 10 million records,
there is no way I can do the excel thing.
James A. Fortune wrote:
> johngl wrote:
> > I just realized the problem. The sum of units is greater than zero, so
> > all are counted, problem is four of the records have to be deleted. I
> > should have added that there is a date field and all the adjustments
> > are made on the same day. I added this below for reference. Even if I
> > group by date, I still get a positive value for August, and the 3
> > visits for that day when in fact there is only one (why they do this is
> > beyond me).
> >
> > johngl wrote:
> >
> >>I just ran your code, here is an actual set of 6 records, same patient:
> >>PTID CODE UNITS DATE
> >>1 100 1 Aug 20
> >>1 100 1 Aug 20
> >>1 100 -1 Aug 20
> >>1 100 1 Sept 9
> >>1 100 -1 Sept 9
> >>1 100 1 Sept 9
> >>
> >>These are all the same code, if it works on one, it will work on all
> >>codes. The correct result is 2 units and 2 visits, the bottom four are
> >>a wash, leaving the top two. Your code produced 6 visits and 2 units. I
> >>would be happy just to get the visits. Not sure why your code did not
> >>work, it looks correct.
> >>
> >>Any suggestions? Thanks again.
> >>
> >>James A. Fortune wrote:
> >>
> >>>johngl wrote:
> >>>
> >>>>This is another question about eliminating accounting entries in a
> >>>>clinical database, yet retaining the valid record. Latest wrinkle is
> >>>>being able to sum the visits to the doctor, and the units of services
> >>>>received. Here's the problem: If a visit is wrongly coded, then a
> >>>>reverse entry is made as follows.
> >>>>PTID CODE UNITS
> >>>>1 100 2
> >>>>1 100 -2
> >>>>1 110 2
> >>>>
> >>>>This is actually one visit for code 110 and the patient received 2
> >>>>units of service. Thanks to a member's advice, I used:
> >>>>
> >>>>SELECT ptid, code, sum(units) AS unit
> >>>>FROM PTDB
> >>>>GROUP BY ptid, code
> >>>>HAVING (((sum(units))<>0));
> >>>>
> >>>>This gives me a correct unit of service (I checked manually), but I
> >>>>still need a field with a count of 1 for the visit. Most reports focus
> >>>>on the number of visits taken, not how long much time you spent in the
> >>>>office. Is there a way to get both sum of units and count of visits?
> >>>>This is pretty basic stuff to most people, but it's killing me. Thks.
> >>>>
> >>>
> >>>Air SQL:
> >>>
> >>>SELECT sum(1) AS visits, ptid, code, sum(units) AS unit FROM PTDB GROUP
> >>>BY ptid, code HAVING (((sum(units))<>0));
> >>>
> >>>Only records that have not been cancelled will show up so the number of
> >>>visits should be the same as the number of records returned by the query.
> >>>
> >>>James A. Fortune
> >>>(E-Mail Removed)
> >>>
> >>>Wisconsin produces more cranberries than any other state. --
> >>>http://www.wiscran.org/
> >
> >
>
> I see that I made a poor guess about what your data looks like because
> of the small sample. I'll try to take a look at it again tomorrow night.
>
> James A. Fortune
> (E-Mail Removed)

This is just a passing thought...

There's no easy way to tell, in your example, that the Code=100 was, in
fact, an invalid entry.

What if, instead of adding in a counter/reversing entry, you added a single
Yes/No field to the table. The purpose of the Yes/No field is to indicate
"Disregard This Row". Any invalid entries have the value in this field set
to "Yes" (via a checkbox on a form).

Now, all you need to do to get a count of (valid) visits is to use the
Totals query and count the rows where the checkbox/field is NOT "Yes".

Just a thought...

Regards

Jeff Boyce
Microsoft Office/Access MVP

"johngl" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> This is another question about eliminating accounting entries in a
> clinical database, yet retaining the valid record. Latest wrinkle is
> being able to sum the visits to the doctor, and the units of services
> received. Here's the problem: If a visit is wrongly coded, then a
> reverse entry is made as follows.
> PTID CODE UNITS
> 1 100 2
> 1 100 -2
> 1 110 2
>
> This is actually one visit for code 110 and the patient received 2
> units of service. Thanks to a member's advice, I used:
>
> SELECT ptid, code, sum(units) AS unit
> FROM PTDB
> GROUP BY ptid, code
> HAVING (((sum(units))<>0));
>
> This gives me a correct unit of service (I checked manually), but I
> still need a field with a count of 1 for the visit. Most reports focus
> on the number of visits taken, not how long much time you spent in the
> office. Is there a way to get both sum of units and count of visits?
> This is pretty basic stuff to most people, but it's killing me. Thks.
>

Well, I think there is two potential problems there....
1. If I eliminate all the negative entries, I still have an invalid
positive record, I have to eliminate the two records, when the only
difference is the negative value of the unit. Real problem is combining
an accounting and clinical database in the first place, but that is
another story.
2. I could create an adjustment table of the sums of all negative
values by code, and deduct that from the subtotals of visits by code.
However, that process destroys the integrity of the record, so I cannot
look at variances between codes and age and sex, for example. Can't
analyze aggregated data.

Simple but difficult problem. The total negative records is only a
small percentage of total cases, I could just eliminate the negative
cases, leaving one positive wrong record for 100K wrong vists out of 2
million.


Jeff Boyce wrote:
> This is just a passing thought...
>
> There's no easy way to tell, in your example, that the Code=100 was, in
> fact, an invalid entry.
>
> What if, instead of adding in a counter/reversing entry, you added a single
> Yes/No field to the table. The purpose of the Yes/No field is to indicate
> "Disregard This Row". Any invalid entries have the value in this field set
> to "Yes" (via a checkbox on a form).
>
> Now, all you need to do to get a count of (valid) visits is to use the
> Totals query and count the rows where the checkbox/field is NOT "Yes".
>
> Just a thought...
>
> Regards
>
> Jeff Boyce
> Microsoft Office/Access MVP
>
> "johngl" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > This is another question about eliminating accounting entries in a
> > clinical database, yet retaining the valid record. Latest wrinkle is
> > being able to sum the visits to the doctor, and the units of services
> > received. Here's the problem: If a visit is wrongly coded, then a
> > reverse entry is made as follows.
> > PTID CODE UNITS
> > 1 100 2
> > 1 100 -2
> > 1 110 2
> >
> > This is actually one visit for code 110 and the patient received 2
> > units of service. Thanks to a member's advice, I used:
> >
> > SELECT ptid, code, sum(units) AS unit
> > FROM PTDB
> > GROUP BY ptid, code
> > HAVING (((sum(units))<>0));
> >
> > This gives me a correct unit of service (I checked manually), but I
> > still need a field with a count of 1 for the visit. Most reports focus
> > on the number of visits taken, not how long much time you spent in the
> > office. Is there a way to get both sum of units and count of visits?
> > This is pretty basic stuff to most people, but it's killing me. Thks.
> >

Let's assume your 10M record table is table A.
Append all negatives in table A to a new table B.
Change all negatives in table B to positives (*-1).
Delete negatives from table A.
Join table A and table B deleting matches in table A.
What's left in table A is good stuff?

--
Doug F.


"johngl" wrote:

> Well, I think there is two potential problems there....
> 1. If I eliminate all the negative entries, I still have an invalid
> positive record, I have to eliminate the two records, when the only
> difference is the negative value of the unit. Real problem is combining
> an accounting and clinical database in the first place, but that is
> another story.
> 2. I could create an adjustment table of the sums of all negative
> values by code, and deduct that from the subtotals of visits by code.
> However, that process destroys the integrity of the record, so I cannot
> look at variances between codes and age and sex, for example. Can't
> analyze aggregated data.
>
> Simple but difficult problem. The total negative records is only a
> small percentage of total cases, I could just eliminate the negative
> cases, leaving one positive wrong record for 100K wrong vists out of 2
> million.
>
>
> Jeff Boyce wrote:
> > This is just a passing thought...
> >
> > There's no easy way to tell, in your example, that the Code=100 was, in
> > fact, an invalid entry.
> >
> > What if, instead of adding in a counter/reversing entry, you added a single
> > Yes/No field to the table. The purpose of the Yes/No field is to indicate
> > "Disregard This Row". Any invalid entries have the value in this field set
> > to "Yes" (via a checkbox on a form).
> >
> > Now, all you need to do to get a count of (valid) visits is to use the
> > Totals query and count the rows where the checkbox/field is NOT "Yes".
> >
> > Just a thought...
> >
> > Regards
> >
> > Jeff Boyce
> > Microsoft Office/Access MVP
> >
> > "johngl" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > This is another question about eliminating accounting entries in a
> > > clinical database, yet retaining the valid record. Latest wrinkle is
> > > being able to sum the visits to the doctor, and the units of services
> > > received. Here's the problem: If a visit is wrongly coded, then a
> > > reverse entry is made as follows.
> > > PTID CODE UNITS
> > > 1 100 2
> > > 1 100 -2
> > > 1 110 2
> > >
> > > This is actually one visit for code 110 and the patient received 2
> > > units of service. Thanks to a member's advice, I used:
> > >
> > > SELECT ptid, code, sum(units) AS unit
> > > FROM PTDB
> > > GROUP BY ptid, code
> > > HAVING (((sum(units))<>0));
> > >
> > > This gives me a correct unit of service (I checked manually), but I
> > > still need a field with a count of 1 for the visit. Most reports focus
> > > on the number of visits taken, not how long much time you spent in the
> > > office. Is there a way to get both sum of units and count of visits?
> > > This is pretty basic stuff to most people, but it's killing me. Thks.
> > >
>
>

That should work, will try now. I assumed this would require a select
statement. Your idea should run faster, other select statement takes 3
hours. Thanks, John

Doug F. wrote:
> Let's assume your 10M record table is table A.
> Append all negatives in table A to a new table B.
> Change all negatives in table B to positives (*-1).
> Delete negatives from table A.
> Join table A and table B deleting matches in table A.
> What's left in table A is good stuff?
>
> --
> Doug F.
>
>
> "johngl" wrote:
>
> > Well, I think there is two potential problems there....
> > 1. If I eliminate all the negative entries, I still have an invalid
> > positive record, I have to eliminate the two records, when the only
> > difference is the negative value of the unit. Real problem is combining
> > an accounting and clinical database in the first place, but that is
> > another story.
> > 2. I could create an adjustment table of the sums of all negative
> > values by code, and deduct that from the subtotals of visits by code.
> > However, that process destroys the integrity of the record, so I cannot
> > look at variances between codes and age and sex, for example. Can't
> > analyze aggregated data.
> >
> > Simple but difficult problem. The total negative records is only a
> > small percentage of total cases, I could just eliminate the negative
> > cases, leaving one positive wrong record for 100K wrong vists out of 2
> > million.
> >
> >
> > Jeff Boyce wrote:
> > > This is just a passing thought...
> > >
> > > There's no easy way to tell, in your example, that the Code=100 was, in
> > > fact, an invalid entry.
> > >
> > > What if, instead of adding in a counter/reversing entry, you added a single
> > > Yes/No field to the table. The purpose of the Yes/No field is to indicate
> > > "Disregard This Row". Any invalid entries have the value in this field set
> > > to "Yes" (via a checkbox on a form).
> > >
> > > Now, all you need to do to get a count of (valid) visits is to use the
> > > Totals query and count the rows where the checkbox/field is NOT "Yes".
> > >
> > > Just a thought...
> > >
> > > Regards
> > >
> > > Jeff Boyce
> > > Microsoft Office/Access MVP
> > >
> > > "johngl" <(E-Mail Removed)> wrote in message
> > > news:(E-Mail Removed)...
> > > > This is another question about eliminating accounting entries in a
> > > > clinical database, yet retaining the valid record. Latest wrinkle is
> > > > being able to sum the visits to the doctor, and the units of services
> > > > received. Here's the problem: If a visit is wrongly coded, then a
> > > > reverse entry is made as follows.
> > > > PTID CODE UNITS
> > > > 1 100 2
> > > > 1 100 -2
> > > > 1 110 2
> > > >
> > > > This is actually one visit for code 110 and the patient received 2
> > > > units of service. Thanks to a member's advice, I used:
> > > >
> > > > SELECT ptid, code, sum(units) AS unit
> > > > FROM PTDB
> > > > GROUP BY ptid, code
> > > > HAVING (((sum(units))<>0));
> > > >
> > > > This gives me a correct unit of service (I checked manually), but I
> > > > still need a field with a count of 1 for the visit. Most reports focus
> > > > on the number of visits taken, not how long much time you spent in the
> > > > office. Is there a way to get both sum of units and count of visits?
> > > > This is pretty basic stuff to most people, but it's killing me. Thks.
> > > >
> >
> >