Results 1 to 14 of 14
  1. #1
    Join Date
    Aug 2021
    Posts
    5

    Subform total of details. Get count instead of amount.


    New user to Access and this forum. The total in the footer of my subform shows the count of the items listed instead of the total dollar amount. Both are in currency format, so the count shows as $2.00 when two items are listed. My sense is I'm missing a step somewhere.
    Attached Thumbnails Attached Thumbnails 20210801_Access_subform_total.jpg  

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    That is a continuous subform that the summed control is on? Then I think you want to take a look at DSum function and include criteria so that only the related numbers are summed.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    your tuition control is a combo - what is the rowsource to the control and please provide some example data

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Surely if you want Count rather than Amount, then your footer textbox control source should be =Count(Tuition) instead of =Sum(Tuition)
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    Join Date
    Aug 2021
    Posts
    5
    Here is the form and subform with example data. The Amount Due should be $240, the sum of the tuition, instead of a count of 2.Click image for larger version. 

Name:	20210802_Access_subform_total.JPG 
Views:	26 
Size:	65.8 KB 
ID:	45893

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    answer to post #3? problem is you are probably summing ID rather than the amount.

    What do you get if one of the amounts is different to $120?

  7. #7
    Join Date
    Aug 2021
    Posts
    5
    Answer to post #3: SELECT [Class Table].[ClassID], [Class Table].[ClassTuition] FROM [Class Table];

    This is my first Access database. I was thinking that whatever the contents of the result of the above source could be summed...

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    in your combo properties I bet your bound column is column 1, column count is 2 and column widths something like 0;25mm. column 1 is your ID column.

    Perversely, in properties, column 1 is the first column. but when referring to the control columns in vba or from another control, the first column is 0.

    Given this is a price, I would have thought you would want to store the price and not an FK to your prices table - what if down the line you change the price from $120 to $125? All your old invoices will be updated which I wouldn't think is what you would want

    Either change your formula to

    =sum([Tuition].[column](1))

    which may work

    Better would be to change the bound column to 2 - and in both cases change your fee field in your invoice table to a currency data type.

    I can see the benefit of storing prices as a lookup but would question whether all classes have the same price - what if the nature of the class, the breed or age of the dog results in a different price?

    If everything is the same price, and assuming you want to store the the price and not the PK (as above, standard practice is to store the price), you don't need to bring the PK through in your rowsource at all and may not even need it.

    Finally, I think your rowsource needs an order by clause - if you introduced another price it won't be in order of value. for example

    Code:
    SELECT [Class Table].[ClassID], [Class Table].[ClassTuition] FROM [Class Table] ORDER BY [Class Table].[ClassTuition]
    One final note - it is bad practice to use spaces in table and field names, recommend you remove them before you get much further down the road

  9. #9
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I would have thought it not possible to sum over iterations of the same combo. Since it's a continuous form, there really is only one combo? Nothing I've tried will produce such a result, but then again, my test combo name and the bound field name are not the same and I'd say that is an important distinction. I think in reality we're summing the field in this case, not the combo. In that case, I don't think you can sum a column. I will be happy to be proved wrong, but I still think the answer is in post 2.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    I agree with Ajax, I'm seeing some design choices that may need to be addressed beyond your original question. I would suggest you post a zipped copy of your db for a look-see.

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I think in reality we're summing the field in this case
    but based on what the OP has provided it is likely what is being stored in the field is the FK for the price. It wouldn't surprise me if the field in the table is actually defined as a dreaded lookup which typically causes these sorts of issues

  12. #12
    Join Date
    Aug 2021
    Posts
    5
    My thanks to all who responded! I have indeed decided to take a step back and reassess my database design. I believe I incorrectly defined the relationship between two of my tables and the problems propagated from there. Let's close out this thread while I continue to learn more. Thank you again, and all the best.

  13. #13
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by Ajax View Post
    but based on what the OP has provided it is likely what is being stored in the field is the FK for the price. It wouldn't surprise me if the field in the table is actually defined as a dreaded lookup which typically causes these sorts of issues
    I agreed with the FK thing, just didn't explicitly say so. Your second point is an even better one. Alas, it seems we won't find out if the combo column can be summed. I couldn't get it to work.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  14. #14
    Join Date
    Aug 2021
    Posts
    5
    Just an update from OP. I went back to the drawing board and simplified my design. What a difference. Even my wife likes it! This allowed me to concentrate on learning how to send e-mails from access for selected invoices, and that also works. If you're at all curious, my wife and I had been using PayPal to invoice our clients, but PP changed their terms and conditions again to the point where we could no longer agree, so thought I'd try a DIY approach. Again, thanks to all of you for your kind help!!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 05-25-2015, 05:02 AM
  2. Running Total on Amount for each Month?
    By aellistechsupport in forum Queries
    Replies: 4
    Last Post: 04-24-2014, 01:18 PM
  3. Replies: 2
    Last Post: 04-02-2014, 09:15 AM
  4. total amount
    By sdc1234 in forum Queries
    Replies: 12
    Last Post: 02-19-2014, 03:37 PM
  5. Replies: 2
    Last Post: 10-25-2010, 10:45 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums