Calculations in Reports - Nightmare!

[PappyEnza]

Hi, I am having a major brain ache trying to perform a calculation in a Report. I am convinced I'm tackling it the wrong way but cannot see the wood for the trees. I am a self taught access user and I am not familiar with SQL or VBA beyond the very basic and Access wizards...Sorry!

OK, I have a number of query steps culminating in producing an output showing the details of people to whom my company has paid compensation to on a particular project. Within the database a person can and probably will have a number of payments recorded against a claim with each payment having its own unique number. The claimants details are help in one table (each claimant has a Unique Identifier assigned) and a figure representing the last estimate of claim is also held here.

Each time a payment is made a new settlement report is raised (with a unique number) and the details of the payment are held in different tables, all linked back to the claimant and their own Unique Identifier. The query will output multiple lines per claimant depending on the number of payments made; the single figure of last estimate of claim is output on each line, obviously.

I can build the report without any difficulty and I can make the report only show the last estimate of claim once by means of a section header; however I want to calculate in the report footer the total net liability for the project by what should be a simple calculation of Last Estimate of Compensation - Total Payments Made = Net Total Liability. The problem arises in that the report adds up all of the Last Estimates for each claimant even though this is really only one figure per claimant for example if there are 10 lines of payments for a claimant the report sums 10 x the Last Estimate because the query has output this figure on each payment line. It is driving me nuts!!!

I know I'm approaching this wrong but cannot see what I should be doing let alone what I'm doing wrong.



Can anyone understand the above gibberish and point me in the right direction?

[ranman256]

Do as many calculations as you can in the QUERY ,(rather than the report).
The only calcs needed on reports are summations ...=Sum([myField])

[June7]

Options:

1. a GROUP BY (Totals) query that aggregates the payments for each claimant then include that query in the RecordSource, the sum figure will already be available and then won't need the Sum() in textbox on report

2. subreports

3. DSum() domain aggregate function