Incorrect Calculations in Report

[Bridiewms]

Hi,

I have a report which displays information about money asked for from a company (column 3). and total money paid (column 7) and a calculation in green atthe end for outstanding amount (column 8). The red figures are correction figures to get the outstanding amount to 0.

An invoice is raised asking for money to be paid in one table (SOV) and the payment break down is input into another table (SOVPayment).

I seem to be getting a problem when an invoice raised (SOV) has more than one payment (SOVPayment).

As shown in the diagram attached. If you add together all the figures shown in the 3rd column it should come to around £152,108 which is around £9042 less than what has been calculated in blue at the bottom of the image, meaning that because of the 2 payments made against invoice 900139 the amount has been added twice.

I dont understand why this happens...I have grouped information by SOV ID which I thought would solve the problem of duplicating the amount. Is there any other way I should be doing this?

[aytee111]

What is your record source? If it is a query it will be much easier to debug a query - then you can put the actual SOV id into the criteria of the query and make sure you get the right figures prior to coming into the report.

[Bridiewms]

All the information is linked up in a query from tables such as person details, supplier information, SOV details and SOV payment details but the only criteria for the query is a parameter query where you specify the start and end dates for the data shown.

All the calculations shown on the diagram above are ones calculated on the report

[aytee111]

Get your query working correctly first, that is where the problem is. Unless you are using a summary only report? Use the Totals feature for the query and sum the amounts.

[Bridiewms]

No its not a summary report. So I should jsut work out all the calculations from the report in the query instead?

[Bridiewms]

Just tried the query with group by and the sum of on the amount paid. Seems to work! Such an easy way around it! Thank you so much its been riving me insane!! Should I be doing all my calcuations for all my reports in the queries instead of in the reports or does it only apply to this instance?

[aytee111]

Doesn't really make any difference to the result, it is your choice. Personally I prefer to get my query working first before trying to attach it to forms or reports, for me it is easier to troubleshoot, easier to make changes later on down the road.

Glad it is working now.