Results 1 to 13 of 13
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Table Amount; Sum (03)

    I have a sample database which represents collections/receipts. Within the DB there is a "Receipts and ReceiptsDetail" table having a set relationship with each other.

    Is it possible to have the total amount of the ReceiptsDetail populate the Receipts table? In my example the details total to $100.00.

    I have attached the sample database.

    Thanks,
    John

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Table Amount; Sum (03)

    You don't want to do that, as it is a non-normalized situation. The biggest potential problem is that this Totals field doesn't get updated every time a detail record is added/changed/deleted.

    Instead, plan on getting the Total by summing the detail records. Depending on the situation, it can be done with a subquery or DSum().
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table Amount; Sum (03)

    Mark,

    I tried your suggestion regarding of summing the detail records through a subquery. Unfortunately the field "amount" in the "receipts" table does not get populated. I can however select the correct amount from what appears to be a combobox in the field.

    Still puzzled.

    Thanks,
    John

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Table Amount; Sum (03)

    As Mark noted, you shouldn't store the amount in the Receipts table. In fact, you should *not* have an Amount field in the Receipts table at all. The amount is derived information, and in general derived information should not be stored in a table, but calculated dynamically in a query.

    See the attached version. The query qryReceipts returns the records of the Receipts table with the calculated amount. You can use this query as record source for forms and reports. Note that the ID and Type field can be edited, but the Amount field cannot be modified, since it is a calculated field.

    Note: I removed the duplicate indexes from the table (they take up unnecessary space) and turned off subdatasheets.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Table Amount; Sum (03)

    Mark's point is that you shouldn't try to store the amount in the receipts table. Instead, you sum the details for that receipt when you need that total amount on the fly, so to speak. That way, if a correction is made to the details, the correction automatically shows up when you calculate the total.
    Charlotte

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table Amount; Sum (03)

    Hans,

    Thank you. As they say, "A picture is worth a thousand words".

    Now if I can only determine who "they" are. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    Regards,
    John

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table Amount; Sum (03)

    Hans,

    You stated that the qryReceipts can be used in "forms" to return the total amount. In my attempt to generate a receipts form/sub-form (I got this to work), I want to include in the main form the total amount from the qryReceipts. I would assume that there needs to be an expression in the field to return the $100. I tried setting up a relationship between the ReceiptsDetail table and the qryReceipts on the ID but encounter an issue where the relationship never completes (just hangs there as if it were trying to establish one).

    Thanks,
    John

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Table Amount; Sum (03)

    Introduce a text box on the form footer of the sub form with a Control source of =Sum(Amount), let's call this text box textSumAmount.

    Then put the following as the control source of the amount on the main form:

    =[Forms]![Receipts]![ReceiptDetails Subform]![textSumAmount]

    I have included an amended database, which does not include the query.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Table Amount; Sum (03)

    My idea was:

    1) Change the Record Source property of the main form from Receipts to qryReceipts.
    2) Change the Control Source property of the text box for the total (Text8 on your form) to Amount.

    But Patt's suggestion works very well.

  10. #10
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table Amount; Sum (03)

    Hans,

    I have changed the "data type" from text to number for both the "receipts and receiptdetails" tables; ID and ReceiptsDID respectively.

    I understand your suggested approach for the expression in the query "qryReceipts":

    Amount: CCur(DSum("Amount","ReceiptDetails","ReceiptsDID=" & Chr(34) & [ID] & Chr(34))) Chr used because data types are set to text

    I am currently having an issue with the results being returned as #Error because of the data type change made to number. I would have thought the expressions would be something like this:

    Amount: CCur(DSum("Amount","ReceiptDetails","ReceiptsDID=" & [ID]))


    Thanks,
    John

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Table Amount; Sum (03)

    That expression looks OK, and it works in the modified version of the database I attached earlier in this thread. I changed the data type of ID and ReceiptsDID to a Long Integer.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Table Amount; Sum (03)

    If your Receipts table contains IDs without related records in ReceiptDetails, the DSum function will return Null, and this will cause CCur to return #Error. This has nothing to do with whether ID is a text field or a number field.

    If null values are the cause of your problem, you can use

    Amount: CCur(Nz(DSum("Amount","ReceiptDetails","ReceiptsDI D=" & [ID]),0))

    in the query. The Nz function replaces null results with a 0 (zero), so that CCur always sees a valid number.

  13. #13
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table Amount; Sum (03)

    It looked good to me as well. I tried it about a dozen times but kept on getting the #Error. A few more attempts and it worked.

    Strange... <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

    Regards,
    John

Posting Permissions

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