Query to total all numbers in a column

[Rik_StHelens]

Hi,

I have a query which makes an invoice for us to send out to customers. I want to add a field which displays the total of their outstanding balance over several months.

At present i have the following expression which doesn't work, but i think im on the right lines.

Total: Sum(IIF(IsNull[Outstanding Bal],0,[Outstanding Bal]))

although i might be going the long way round and there is a simpler way?

Many thanks

 

[DannySeager]

I'm not exactly sure what you are trying to do (I don't know your data structure or report layout) but have you tried

=Sum(Nz([Outstanding Bal],0))

 

[Rik_StHelens]

I'm not exactly sure what you are trying to do (I don't know your data structure or report layout) but have you tried

=Sum(Nz([Outstanding Bal],0))

Hi,
sorry i probably wasnt clear enough

What i have created is an invoice, which is output to a report.

The query that runs currently displays all the individual purchases made by a customer, and the amount of each purchase is displayed in the outstanding bal field.

What i want to do is add up all the outstanding bal, to make a total outstanding balance.

e.g.

Purchase 1 = £10
Purchase 2 = £20
Purchase 3 = £15

Total = £45

 

[DannySeager]

Here's an example (it's not pretty )

 

[Rik_StHelens]

Here's an example (it's not pretty )

I've put in your example, and it is just replicating the data at the minute.

e.g.

Purchase No Outstanding Bal Total Bal
Purchase 1 £20 £20
Purchase 2 £15 £15
Purchase 3 £20 £20

But all i want from Total Bal is a single figure, the Sum of all outstanding Balances, so in the above case just:

Purchase No Outstanding Bal Total Bal
Purchase 1 £20
Purchase 2 £15
Purchase 3 £20 £55


I noticed on access when i run the query to a datasheet i have a dropdown box at the bottom which allows me to display the Sum of all the values in a column, this is what i want, but output to a report, i will attach an image of our report to show what i mean

Thanks again.

 

[Rik_StHelens]

I've put in your example, and it is just replicating the data at the minute.

e.g.

Purchase No Outstanding Bal Total Bal
Purchase 1 £20 £20
Purchase 2 £15 £15
Purchase 3 £20 £20

But all i want from Total Bal is a single figure, the Sum of all outstanding Balances, so in the above case just:

Purchase No Outstanding Bal Total Bal
Purchase 1 £20
Purchase 2 £15
Purchase 3 £20 £55


I noticed on access when i run the query to a datasheet i have a dropdown box at the bottom which allows me to display the Sum of all the values in a column, this is what i want, but output to a report, i will attach an image of our report to show what i mean

Thanks again.

Sorry it wouldnt attach,

i've had to copy the report to a blank db file.

if you open it in design view, and you should see the total due column. It is there that i would like to display the sum of all outstanding balances for a customer

 

[Rik_StHelens]

My bad

i was entering the exp in query design not report design.

Thanks very much for your help

 

[Rik_StHelens]

my bad

was inputting it into the query not the report design

thanks for your time

 

[DannySeager]

you're welcome.

glad you got it all sorted.