Trying to clean out the noise in a query

[beegee]

Hi all,
I wrote a query that gives me information like this:




Claim line 1: pt name: scarecrow | doc: wizard | surgery: heart transplant | paid: 1 (1 = paid) | paid amt: $20
Claim line 2: pt name: scarecrow | doc: wizard | surgery: heart transplant | paid: -1 (-1 = returned payment) | paid amt: -$20

Claim line 3: pt name: scarecrow | doc: wizard | surgery: heart design and implant | paid: 1 | paid amt: $50



When I run just a simple count of my paid surgeries, based on the above example, I will only get 1 surgery because the wizard got paid for only 1 and the transplant was cancelled out. The question is, how do I write the query to just show the claim that was not cancelled out (claim line 3)?

Thank you in advance for your help on this question (which hopefully makes sense).

[Preston]

Hi all,
I wrote a query that gives me information like this:


Claim line 1: pt name: scarecrow | doc: wizard | surgery: heart transplant | paid: 1 (1 = paid) | paid amt: $20
Claim line 2: pt name: scarecrow | doc: wizard | surgery: heart transplant | paid: -1 (-1 = returned payment) | paid amt: -$20

Claim line 3: pt name: scarecrow | doc: wizard | surgery: heart design and implant | paid: 1 | paid amt: $50



When I run just a simple count of my paid surgeries, based on the above example, I will only get 1 surgery because the wizard got paid for only 1 and the transplant was cancelled out. The question is, how do I write the query to just show the claim that was not cancelled out (claim line 3)?

Thank you in advance for your help on this question (which hopefully makes sense).

The best way to do it is with a Count aggregation. Select s.[surgery], count(s.[pt name]) as operationcount from (select distinct [pt name],[doc],[surgery] from tablename) as s

But really, there should be a table for surgeries, and these payments should be in a separate table that has a many to one relationship to the surgery table.

[andy49]

could you try a grouped query using sum (Paid) first then run a select query to search for non-zero Paid field?

scarecrows op by the wizard wouldn't show as the grouped query would cancel out as sum (1 and -1) = 0

[beegee]

Hi Andy, Thank you for your response.

Hmmm... I tried that. I got both the $20 transplant and $50 heart design and implant, since they were both technically paid. Somehow I want Access to recognize only the payments for the pts that were paid and not returned. Even though they are in different rows.

[beegee]

Is that in sql? Can I do that in Access? There is a table for the surgeries and for what the docs get paid. I also made this into a separate table. I am so tempted to go through this manually and delete all of the kicked out payments since I can't figure out how to write this in Access.

[beegee]

The best way to do it is with a Count aggregation. Select s.[surgery], count(s.[pt name]) as operationcount from (select distinct [pt name],[doc],[surgery] from tablename) as s

But really, there should be a table for surgeries, and these payments should be in a separate table that has a many to one relationship to the surgery table.

Is that in sql? Can I do that in Access? There is a table for the surgeries and for what the docs get paid. I also made this into a separate table. I am so tempted to go through this manually and delete all of the kicked out payments since I can't figure out how to write this in Access.

[andy49]

SELECT tbltest.name, tbltest.doctor, tbltest.surgery, Sum(tbltest.paid) AS SumOfpaid, Abs([paidAmt]) AS Paid
FROM tbltest
GROUP BY tbltest.name, tbltest.doctor, tbltest.surgery, Abs([paidAmt])
HAVING (((Sum(tbltest.paid))<>0));

My second shot

tbltest was just my table with your data

[beegee]

My second shot

tbltest was just my table with your data

Good morning/afternoon Andy,
That's really funny that you named it "tbltest" since I called it that as well... (could I really be on my way to being an Access expert???).

I will definitely try the Abs function and let you know how it goes. I think that is exactly what I am looking for, though. So thank you very much!

(btw, this is only my second time posting a question here, so should I mark it "solved" before or after I try it?)

[andy49]

No only mark as solved when the matter is done. My solution might not work for some reason and others can then look closely at it too

[beegee]

It worked!!! I checked was able to check against the claims and see that in fact this is exactly what happened and what got paid and finalized!!! Woo-hoo!!! Abs is my new best friend Thank you, Andy! Now where's that Solved button...