Calculated Field Issue

Woelfe

New Member
Joined
Sep 17, 2002
Messages
35
I have a problem I am trying to find a work around for. I have a list in excel from our controller of health insurance fees that have been applied to student accounts, and have also been removed (due to waiving insurance). What I am trying to do is filter the list out based on who has a zero balance when adding their multiple health insurance fees together.

For example, John Smith was charged the fee on 7/1 for $1688, but submitted a waiver on 7/31 to have the charge removed. It will show like this:

Name Date Amt
J Smith 7/1 <-$1688>
JSmith 7/31 $1688

If I were to calc these 2 entries it would come out to zero. Does anyone know of a good way to do this? I tried tinkering with a query and a report to get it to do what I want, but it comes up bad. I think the report is probably the way to go if I could figure out how to do the sub-total of the field.

Any ideas are very appreciated.

-Mike
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Woelfe

New Member
Joined
Sep 17, 2002
Messages
35
Ok, for whatever reason, the extra 2 hours of sleep last night helped me figure out what I did wrong on the sub-total part. The report I did with the wizard comes up based on ssn being grouped, and I have summed the trans_amt field. Is there a slick way to only show those students with a zero amount for the sub-total?
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
You can write another query over your query that currently is supplying the records for your report. It looks like you still want the detail in your report of the charge, then the credit, so this second query will give you all your subtotals, precalculated before the report is run. This use this second query joined to the first query, selecting only those students that are shown in the second query to have a zero balance, but use the fields from the first query in your report. Hope this makes sense and helps.
 

Woelfe

New Member
Joined
Sep 17, 2002
Messages
35
You can write another query over your query that currently is supplying the records for your report. It looks like you still want the detail in your report of the charge, then the credit, so this second query will give you all your subtotals, precalculated before the report is run. This use this second query joined to the first query, selecting only those students that are shown in the second query to have a zero balance, but use the fields from the first query in your report. Hope this makes sense and helps.

Thanks for the reply Vic. What I am trying to do now is only show the records in the report that the sub-total of the trans_amt is equal to zero. Right now the report shows everything, whether they have a charge or not. I didn't find a way to filter based on the =sum field in the report.

I'm not sure the 2nd qry in this case would work, or maybe I'm not understanding it exactly. The first query doesn't actually have the running total of the balance. Only the amount of the transaction. Maybe something I can add to it to do this?
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
The second query would be used to calculate the subtotal that you will be getting in your report. By doing it in a query ahead of time, you can know which trans-amt subtotals will be zero, and by doing a join between the first query and the second query (thus creating a third query) you can select only those records that will produce the zero subtotals. The report would be based on the third query, which will give you all the detail records of the groups that will have zero subtotals. Hope that is a little clearer.
 

Forum statistics

Threads
1,137,366
Messages
5,681,072
Members
419,950
Latest member
BeckiJae

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top