Generating totals in a monthly report

[jaggas]

I've got a simple invoice database with 10 amount fields,
that needs to be summed up intoa Total including Gst box on a monthly report.

Report only has Date, Invoice Number And Total Amount fields on it, so I need to pull the information from table and sum it into Total Amount.

ie. = sum ( t1 +t2 + t3 + t4 + t5 + t6 + t7 + t8 + t9 + t10)
and sum above divided by 10% then added together to form total.

I'm just not sure how to do it, everything I've tried so far ain't worked.

any help would be appreciated.

Its kinda doing my head in & every example I've looked at is only doing maths with 1 field.

btw pulled my back muscle yesterday so brain just can't think clear :/

 

[rzw0wr]

Is there data in every field?
Are t1-t10 numbers?

Dale

 

[BeyondBeige]

If I were doing this I'd base my report on a query; write the query to return the correct data first, then use the query as basis for the report.

Create a totalled query for your data with three fields:
Date Group by
Invoice Number Group by
Total Amount Sum

build Total Amount as
Total Amount: t1 +t2 + t3 + t4 + t5 + t6 + t7 + t8 + t9 + t10
(I'm assuming t1 - t10 to be your 10 amount fields)

If as rzw0wr suggests, if not all these fields are completed for each record, wrap each field in an nz:
Total Amount: nz(t1,0) +nz(t2,0) + nz(t3,0) + nz(t4,0) + ... etc

Once you have your data, sort the odd 10% tweak in the report.

 

[jaggas]

Thanks for the help.
stepping through your guide did wonders.

I've got it running.
had a few things wrong in the query and also forgot to add query to report.

btw I've got totals fields with defaults of 0, so I assume I shouldn't need to use the nz() function?

painkillers for back numbed the brain a little too much
regards John

 

[rzw0wr]

jaggas, any time you think a field might contain a null then you should use nz().
What happens in Access is that if a calculation contains a null the answer will be null.

Usually it is better to use the nz() function and be sure than try to find the error.
We are glad to hear you got it working.
Continued success with your project.
Dale