How do I create a Sum total for multiple fields? (1 Viewer)

[Repent]

I have a DB with the following tables/fields:

Table=GLAccount
Field=GLAccountNumber

In the field value, there are 7 different account numbers that I want to sum up (there are many more, but only want to sum seven of them). The account numbers represent different product lines.
They are:

410000.00
412500.00
415000.00
417500.00
420000.00
421000.00
431000.00

Each of these account numbers has a value associated with it that represents a sales dollar figure.

I need a formula that will add up the values represented by the account numbers and place that value in the report footer next to a field I've labeled as "Balance per GL".

Can someone point me in the right direction?

thank you all;
chris

 

[kevlray]

Are you using a running total that evaluates only when a formula is true? I think that might be what you want. Otherwise you could use shared variable(s) to do the calculations as needed.

 

[Repent]

Are you using a running total that evaluates only when a formula is true? I think that might be what you want. Otherwise you could use shared variable(s) to do the calculations as needed.

I'm using a running total that sums if it is true that the account numbers equal the ones in my post. Problem now is that there are numerous repeats of the same data. Need a way for the formula to only calculate the first instance of the account number it comes across, not summing up every instance of the account number. For example, acct # 410000 carries the value of $667,000.00. there are several instances of this because of the way invoices are dealt with in the Businessworks DB. I need the formula to only sum one instance of the account numbers it finds. Below is the code I have so far. I created a running total field that uses that formula and is set to "on change of field"="GLAccount.GLAccountNumber"

 if {GLAccount.GLAccountNumber} in [410000.00,412500.00,415000.00,417500.00,420000.00,421000.00,431000.00]
 then {GLAccount.MonthBeginBalance} else 0

 

[kevlray]

Sorry for the delay on replying, I was out a couple of days and been trying to get caught up.

I cannot think of an easy way of doing this. It seems you would need a group of flags (shared variables), one for each of the account numbers. These would be set the first time the account number was encountered and then used to make sure that no more values would be added.

If I have the time I will see if I can work up some code.

I hope this helps.

 

[Repent]

ok thank you for looking at this.

 

[oumahexi]

Have you tried creating a group for the fields and setting your running total to "on change of group"?

 

[kevlray]

I think the issue is that Repent is only looking for the first row of data per account. I sure this could be done with variables, but it would need a seperate variable(s) for each account.

 

[gaby]

use a query that uses one of the aggregate (totals) functions, such as First() or Last(), in the fields that do not contain duplicate data. In the fields that do contain duplicate data, use the GroupBy() function.

So you could GROUP by account number and then ask for the First or Last record you need also you can SUM the Sales amount field.

 

[kevlray]

Mmm. I never used First() or Last() inside of a group.

 

[gaby]

I'm having a similar issue as yours. Serching for an answer found this article in microsoft support com (can't include the link!)
Article ID: 292634
How to use a query to filter unique data in Access.
It explains how to use group and total functions together. Hope this will help you resolve your problem.
Reading it I was able to retrieve the record but my problem is that I need to combine it with an update and there everything goes wrong.

 

[gaby]

Since the record is repeated and you don't want to add it you can group by account number, print only group totals and instead of using sum you can use max, min or avg function as it will yield the same result.

Even though it might not be a very elegant solution if you don't want to re-engineer your report this might work for you! Please excuse me if you think this isn't a nice way of solving your problem.
Gaby