This is my third 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. The units cannot be less than the visits, you get a least one
unit per visit. 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.

johnds wrote:

Try:

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

And for the love of all that's holy, if you are designing the interface
for which data is being entered, make sure the application the
receptionist uses is not allowed to enter 0 or less. Ideally you do
this in the table structure itself. Whoever designed whatever app is
now being used really dropped the ball on that one.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

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 1 units. I

would be happy just to get the visits. The problem is I think is the
sum of the units will be positive, so all the records will be counted.
Any ideas?

thks,



Tim Marshall wrote:

johnds wrote:

Looks like I misinterpreted you. I was wrong about the negative value -
that's a valid way of zeroing out incorrect transactions. I think the
following should work to get what you want by counting the number of visits:

SELECT ptid, Code, sum(units) AS unit, Count(*) as Visits
FROM PTDB
GROUP BY ptid, Code
HAVING (((sum(units))<>0))


--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

"johnds" <johngl_ca (AT) yahoo (DOT) com> schreef in bericht news:1157757792.646552.226260 (AT) h48g2000cwc (DOT) googlegroups.com...
I guess you are going to need 3 queries to get the results you need.

qryNG1:
SELECT PTDB.PTID, PTDB.Code, Count(PTDB.Code) AS Records, Sum(PTDB.Units) AS Units
FROM PTDB
GROUP BY PTDB.PTID, PTDB.Code
HAVING (((Sum(PTDB.Units))<>0));
==>> Output is PTID-Code-Records-Units

qryNG2:
SELECT PTDB.PTID, PTDB.Code, Count(PTDB.Code) AS NegVisits
FROM PTDB
WHERE (((PTDB.Units)<0))
GROUP BY PTDB.PTID, PTDB.Code;

==>> Output is PTID-Code-NegVisits

3rd query (1st and 2nd query joined on PTID and Code) will give you what you need:
Number of visits = number of records - 2x[NegVisits]
SELECT qryNG1.PTID, qryNG1.Code, qryNG1.Records, [Records]-2*[NegVisits] AS VisitCount, qryNG1.Units
FROM qryNG1 INNER JOIN qryNG2 ON (qryNG1.PTID = qryNG2.PTID) AND (qryNG1.Code = qryNG2.Code);

Maybe someone can pop-in with a one-query-solution where a subquery is used ??

Arno R

Arno R wrote:
That won't work in this case. This problem was multiposted to
microsoft.public.access. My first answer was similar to Tim's.

http://groups.google.com/group/micro...e9702bfd73d030

The 10M records argue against using a subquery.

James A. Fortune
CDMAPoster (AT) FortuneJames (DOT) com

<CDMAPoster (AT) FortuneJames (DOT) com> schreef in bericht news:1157860716.194963.210830 (AT) i42g2000cwa (DOT) googlegroups.com...
I guess you mean a subquery won't work ??
Or do you mean the offered solution won't work ??
Will be slow I guess with that number of records, but it will work.
My initial answer to the OP on a related question was to export the 'good' entries to another table and to use the other table for analyses.

Anyhow: I really *hate* multi-posting. Thanks for pointing that out.
I see a lot of people have put some time on his issue... ARRGGGHHHH!!!

This question was posted here, and 15 min's later same question was posted on microsoft.public.access.
Why oh why are these multi-posters wasting one's time ??

To the OP: Please explain your need of multiposting the question !

Arno R

This was my second posting and I was not aware of the multi-posting
issue. I noticed other multi-postings and thought this was how it was
done. I will not do it again, and can see your point. I apologize for
adding extra work on the problem. I am attempting this morning to
delete records in the 10M database, this avoids using select or
subquerys.

Thanks for all your contributions, I have used all your suggestions,
and continue to work on the problem

John

Arno R wrote:

"johnds" <johngl_ca (AT) yahoo (DOT) com> schreef in bericht news:1157894087.609962.166620 (AT) h48g2000cwc (DOT) googlegroups.com...
OK, thanks for the explanation.
I have seen some multi-posters who don't even explain or react when asked for, and just continue to multipost ...

Glad you seem to be a 'different kind'.
Good luck with your project.

Arno R