i need help calculating values in my report... (1 Viewer)

[Michael Weir]

Hi there!

HERE'S SOME BACKGROUND:

I have a query that identifies the worker's compensation insurance coverage for workers' (who are now my clients) past earnings from 1958 to the present. What my query does is identify a payment period, wages earned, and then insurance coverages during that period (there can be many insurance policies in effect during the same wage period... so my job is to allocate a percentage of coverage for each insurance company during the wage period by showing what perentage of the earnings each policy has based upon the number of days covered during the pay period and then divided by the earnings during that period).

What the report needs to accomplish are the calutations for the pro-rata share of liability each insurance company has during that wage period, based upon their percentage of wages they covered during the pay period.

HERE'S MY CHALLENGE:

The difficulty I am having is that the query, and now the report, assign the total earnings for the pay period for each period of insurance coverage, and I don't know how to show just one wage followed by several different periods of coverage and perform the calculations accordingly. In the report properties for the [PAIDAMOUNT] field, I select HIDE DUPLICATES and then I only SEE the one wage value, but any actual calculations that I try to perform for each insurance policy works off the sum of all the duplicated earnings AND also includes (in any sum criteria) the wages from other pay periods.

WHAT I NEED:

Is to figure out how to perform calcuations within a group of insurance policies for each pay period exclusive of all the others; and,

To somehow filter out the duplication of the wages being reported in the same period for each insurance policy during that period.

I hope this makes sense. If you have any questions, please e-mail me.

Thanks!

Michael Weir

 

[Travis]

Can you give us more table structure info?

 

[Clarence]

Are you doing your calculations in the query or the Report?
If you are doing them in the report then the broad principles are:
1 - Group your report on the field you want the breakdown for
2 - Calculate the totals for each group. This can be a little tricky but basically the SUM function works O.K you can tehn do other calculations based on the results of the group SUM value, this is easier than trying to do the calculations based on the group values (does this make sense?)
3 - You can do report totals in a similar way to the Group totals.

There are a number of restrictions on the number of groups you can use, if you hit these then there are alternative, but more complicated methods.
Hope the above helps