Results 1 to 12 of 12
  1. #1
    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 12:37.

  2. #2
    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. #3
    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})

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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. #5
    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. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Fix your database structure.

    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.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    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. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

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

  10. #10
    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. #11
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    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
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  12. #12
    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
  •