# Thread: How to get the sum in the report

1. Registered User
Join Date
Mar 2004
Posts
660

## Unanswered: How to get the sum in the report

Hello, i have a table that each record maintain 6 donate fields that is money, and 6 date fields. each date relates to one donate. I knew this is not good design. But i have to work on this. I don't want to change the design as it was design by somebody else. Now i need to create a report to have only the most recently date and donate for each record. I use module to have function to get the max date and its donate. Then i have a query that use this function maxDonate.

But when i create a report based on this query, in the report footer, i want to get the total of donate =Sum([MaxDonate]). I got error message:
This expression is typed incorrectly, or it is too complex to be evaluted. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

Any body can help me how to get the total that base on module? Many thanks.
Last edited by yyu; 01-05-06 at 11:37.

2. Registered User
Join Date
Aug 2003
Location
Cleveland USA
Posts
184
Can you post the expression that it can't calculate? Usually, I get that error message because of the query, not the report. So, check the function and make sure your query that's used for the report can run on its own first.

Your report will need to have all six fields to do the computation on the report, and the expression needs to be =Nz([Field1])+Nz([Field2])+Nz([Field3])+Nz([Field4])+Nz([Field5])+Nz([Field6]). Or, you can create this equation in your query.

3. Registered User
Join Date
Mar 2004
Posts
660
MaxDonate: MaxRDateDonate([MaxDate],[Donate],[Donate1],[Donate2],[Donate3],[Donate4],[Donate5],[Donate6])

The query runs fine. I can get the most recently date and donate.

In the report footer i use:
=Sum([MaxDonate})

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
why do it as part of a query, you should be able to do this as aprt of the report

consider adding a column to the detail and set its rowsource= ][mycolumnname#1]+[mycolumnname#2]....+[mycolumnname#n

place some group footers on each relevant boundary
copy the detail columns containing the numeric data you are interested in
change the controlsource to =sum([mycolumnname])
copy the modified grouping to the report footer
jobsagoodun

5. Registered User
Join Date
Mar 2004
Posts
660
i am sorry, i didn't get it. As i need to get the sum of all the record that the most recently donate. Not add all the donate for each record.

6. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102

tblDonate
---------
donate_id
donor_id
donate_date
donate_amount

SELECT MAX(donate_amount)
FROM tblDonate
WHERE donor_id = 'your foriegn key here'

In general, if you find yourself creating a table with a whole bunch of columns like "mycolumn1, mycolumn2, mycolumn3", ask yourself if those columns might be better off in their own table. then you won't run into these kinds of shenanigans.

7. Registered User
Join Date
Mar 2004
Posts
660
Thanks. You means create another table and move the fields to this table. Is that correct? I have a question, why you need two ID in the table:
donate_id
donor_id

Thank you very much!

8. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102

9. Registered User
Join Date
Mar 2004
Posts
660
I got, one is primary key. another is forieign key. Thanks.

10. Registered User
Join Date
Mar 2004
Posts
660
One question, as i need to get the most recently donate. So i have to find the max date then find the donate against that date. How could I do this.

11. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
SELECT *
FROM tblDonate
WHERE donor_id = 'your foriegn key here' AND donate_date = DMAX("[donate_date]", "tblDonate", "donor_id = " & donor_id)

OR

SELECT TOP 1 *
FROM tblDonor
WHERE donor_id = yourDonorId
ORDER BY donate_date DESC

12. Registered User
Join Date
Mar 2004
Posts
660
Thanks, let me try these.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•