Calculated Field Issue

[Woelfe]

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

 

[Woelfe]

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]

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]

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]

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.