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
MPAP...@FortuneJames.com
Wisconsin produces more cranberries than any other state. --
http://www.wiscran.org/
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 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
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
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...
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.
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
John