Sum of Fields Running Error on Report (1 Viewer)

[phillip]

Not sure what I'm doing wrong, but when I use a simple Sum([fields]) on my report it runs #Error. I have included the database and a screen shot of the issue.

 

[Gasman]

Crossposted at https://www.accessforums.net/showthread.php?t=91244

O/P also states they have a workaround.

 

[plog]

Big picture--the thing you are doing wrong is you are treating Access like it's a spreadsheet. Here's your fields:

That's not how databases work. In that 1 screenshot I see 2 huge errors:

1. If you are going to add/subtract fields together (like you do to all those _Disbursement and _Receipt fields), then you shouldn't have separate fields for them. You shouldn't have seperate fields for Receipts and Disbursements--you should either just use negative numbers for Disbursements or add a new field (TransactionType) so that you can differentiate them.

2. Numerated field names. In a database when you feel the need to differentiate fields by adding numbers to them (1Receipt, 2Receipt, etc...), its time for a new table. In that new table, you just have 1 field for the value that is currently in that existing table and another field for all those prefix numbers if in fact they are needed.

You either need to use Access like a database and fix your tables, or use Excel.

Small picture -- you're bumping up against some text limit in your calculation because of the big picture issue I outlined. Here's the Control Source of Text56:

=Sum([BeginBalance]+[1Receipt]-[1Disbursement]+[2Receipt]-[2Disbursement]+[3Receipt]-[3Disbursement]+[4Receipt]-[4Disbursement]+[5Receipt]-[5Disbursement]+[6Receipt]-[6Disbursement]+[7Receipt]-[7Disbursement]+[8Receipt]-[8Disbursement]+[9Receipt]-[9Disbursement]+[10Receipt]-[10Disbursement]+[11Receipt]-[11Disbursement])

That's 321 characters, I believe the Control Source is limited to 255. When you fix your database, this issue goes away because you won't be summing a ton of fields, you will at worse sum 2 fields.

 

[Ken Sheridan]

As has been noted in the AccessForums.net forum and here you have an Access table masquerading as a spreadsheet. If that's what you want why not use Excel?

In a relational database the correct way to model such data would be to enter each transaction in a separate row in a table. With a simple TransactionsCD table for instance with columns CustomerID, TransactionType, TransactionDate, Credit, and Debit columns, the following query would return the aggregated credit, debit, and balance values per customer per transaction type per month:

SELECT
    T1.[CustomerID],
    T1.TransactionType,
    YEAR(T1.TransactionDate) AS TranactionYear,
    MONTH(T1.TransactionDate) AS TranactionMonth,
    SUM(T1.Credit) AS Credits,
    SUM(T1.Debit) AS Debits,
    (
        SELECT
            SUM(Credit - Debit)
        FROM
            TransactionsCD AS T2
        WHERE
            T2.CustomerID = T1.CustomerID
            AND T2.TransactionType = T1.TransactionType
            AND FORMAT(T2.TransactionDate, "yyyymm") <= FORMAT(T1.TransactionDate, "yyyymm")
    ) AS Balance
FROM
    TransactionsCD AS T1
GROUP BY
    T1.[CustomerID],
    T1.TransactionType,
    YEAR(T1.TransactionDate),
    MONTH(T1.TransactionDate),
    FORMAT(T1.TransactionDate, "yyyymm");

This query returns the summation of values over a subset of rows of arbitarary size, rather than the addition of a fixed number of values, which is how a relational database works. To output the results of the query oriented horizontally, as with your present report, the above query would be used as the basis for a crosstab query.

 

[DHookom]

I would like to give @phillip the benefit of the doubt by assuming this was a data report that was produced by an accounting system. With those kind of numbers, it's hard to believe they are all keyed into that table.

 

[June7]

I am able to build expression in ControlSource with more than 255 characters. Review posts 8 and 13 of the cross-post for more info.

Copilot tells me character limit for ControlSource property is 2048. Seems I've seen that number elsewhere as well.

 

[Gasman]

So the O/P's original expression should have been OK?

 

[plog]

Copilot tells me...

Ugh.

 

[DHookom]

A running sum to get the totals would probably work if you removed the BeginBalance from the longer expressions and added it back in as a single value.

 

[June7]

Ugh.

Yeah, I know but it sure saved me the other day trying to figure out some code for Excel to manipulate Word. I was quite surprised.

 

[Gasman]

Yeah, I know but it sure saved me the other day trying to figure out some code for Excel to manipulate Word. I was quite surprised.

It certainly can give you a head start.

 

[xavier.batlle]

After some tests I find out that the controlsource property max length is : 2048, and the maximum number of fields in a sum is 100.
Screen 1

Screen 2

 

[GPGeorge]

Ugh.

AI is neither inherently good nor inherently bad. It's a tool. Used wisely, it is beneficial. Used inappropriately, it can lead to problems.

Off-hand a few other tools of similar utility come to mind:

• Fire
• Motor vehicles
• Hammers
• Knives
• Pharmaceuticals
• Electrical appliances

And you can add to the list.

Used properly tools save time and effort. Misused, they can be involved in inflicting injury and death, or, in the case of AI, embarrassment.

 

[Ken Sheridan]

After some tests I find out that the controlsource property max lenght is : 2048, and the maximum number of fields in a sum is 100.

Which is one good reason the correct methodology in a relational database in a context like the OP's is summation over a set of rows rather than addition of a fixed number of values.