Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Counting Patient visits

1 view
Skip to first unread message

johngl

unread,
Sep 8, 2006, 4:43:07 PM9/8/06
to
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.

James A. Fortune

unread,
Sep 8, 2006, 5:18:59 PM9/8/06
to

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
MPAP...@FortuneJames.com

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

johngl

unread,
Sep 8, 2006, 6:48:25 PM9/8/06
to
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.

johngl

unread,
Sep 8, 2006, 7:16:35 PM9/8/06
to
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

James A. Fortune

unread,
Sep 8, 2006, 7:41:54 PM9/8/06
to

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
MPAP...@FortuneJames.com

johngl

unread,
Sep 8, 2006, 7:51:24 PM9/8/06
to
Thanks, really appreciate it. The file has over 10 million records,
there is no way I can do the excel thing.

Jeff Boyce

unread,
Sep 8, 2006, 7:52:47 PM9/8/06
to
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" <john...@yahoo.com> wrote in message
news:1157748186.9...@m79g2000cwm.googlegroups.com...

johngl

unread,
Sep 8, 2006, 8:40:16 PM9/8/06
to
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.

Doug F.

unread,
Sep 9, 2006, 1:06:01 AM9/9/06
to
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

unread,
Sep 9, 2006, 7:56:55 AM9/9/06
to
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

James A. Fortune

unread,
Sep 9, 2006, 10:54:33 PM9/9/06
to
johngl wrote:
> 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.

I'll wait to see how this turns out. The method for reversing an entry
seemed odd to me as well. You don't really want to run anything like a
subquery on 10M records so some kind of intermediate temporary table
(indexed) could help a lot. The special circumstances you have also
tend to make a VBA solution very attractive, possibly on the order of
ten minutes for 10M records.

Here is a thread with much trickier requirements that discusses some of
the issues:

http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/ec19b0924617da8e

Unless the SQL used is done quite carefully a VBA solution is likely to
be considerably faster than using SQL, perhaps as much as 40 times
faster or more (maybe over 100 times faster on a 10M record table).
Even with requisite care, a decent VBA solution is not likely to take
any longer than the best SQL approach. Also, VBA can deal simply with
complicated criteria that would make a SQL coder's head spin. An
indexed temporary table can get close to a VBA solution, but I have
never seen an indexed temporary table beat VBA for cases where subquery
criteria are used to create the temporary table (be sure to count the
time spent creating the temporary table). Both VBA and the temporary
table method seek to come as close as possible to a single pass through
the table. That said, I think a temporary table approach will be adequate.

James A. Fortune
MPAP...@FortuneJames.com

johngl

unread,
Sep 11, 2006, 9:04:38 AM9/11/06
to
Thanks, I need to take a course in VBA, or hire someone with that
skill. Accessing and processing these records will be an annual event.
In the mean time, I have given-up on SQL because of the time
requirement and complexity. I have successfully created the table using
your method.

John

0 new messages