Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    summing a field (A97)

    I have a query which returns a column of prices. How do I, by query or otherwise, perform a calculation that would return the sum of the prices? I intend to place this value in a combobox in a report.

    Cheers

    Rob
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

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

    Re: summing a field (A97)

    Rob,

    In a report, it is easy to sum values. Place a text box in the report footer section with Control Source

    =Sum([fieldname])

    Replace fieldname by the name of the column you want to sum.

    If you group your report on some kind of category, you can calculate the sum for each group by placing such a text box in the group footer section.

    Note: a combo box is not much use in a report, for the user can't interact with it - in fact, the dropdown arrow of a combo box is not even displayed in a report.

  3. #3
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: summing a field (A97)

    Thanks Hans,

    I knew it would be simple. Actually, I remember having done this before, but my memory isn't what it used to be.

    Cheers

    Rob
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

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

    Re: summing a field (A97)

    Oh well, it happens to all of us. What was it you were asking? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  5. #5
    New Lounger
    Join Date
    Dec 2002
    Location
    Codsall, Staffordshire, England
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: summing a field (A97)

    Hi Hans!

    I have the same problem as Rob. However, the text box on my report shows an error. I am getting my price from a calculation in a query called qrymaketableinvoice. This returns the correct data (a list of 3 prices). In the report footer I have a text box whose control source is set to =Sum( [qrymaketableinvoice]![Expr1]). Please would you tell me where I am going wrong?
    Thanks,
    Katie

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

    Re: summing a field (A97)

    Hello Katie,

    Welcome to the Lounge!

    The name qryMakeTableInvoice makes me think it might be an action query that creates a table. You can't use action queries as record source of a form or report.

    If it is a selection query, is it the record source of the report? If so, you can use =Sum([Expr1]) - no need to include the query name. Also, you would have to use a dot . instead of a bang ! between query name and field name.

    If the query is *not* the record source, you may need a DSum. Let us know if you need this, we will help you with it.

  7. #7
    New Lounger
    Join Date
    Dec 2002
    Location
    Codsall, Staffordshire, England
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: summing a field (A97)

    Thank you Hans for your help! The report is generated by a select query and one of the fields is named "Total". This refers to the price for a particular order. The report lists a number of orders. The report detail is accurate showing three orders with their three different totals. The control source of the text box in the report footer is set to =sum(Total), but I still keep getting an error. I hope this information is more useful.
    Thanks,

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

    Re: summing a field (A97)

    Katie,

    There may be confusion over field names vs control names. General advice is to make control names different from the fields they are bound to; for instance a text box with control source Total would be named txtTotal. Unfortunately, Microsoft doesn't adhere to this. If you have another control named Total on the report, =Sum(Total) would cause an error, so check that. The Formatting (Form/Report) toolbar has a dropdown list with all controls.

  9. #9
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: summing a field (A97)

    Hans,
    I think my brain has finally ceased up. I have been trying many expressions in the text box in the report footer to return sum of a column generated from a query. I have also tried a DSum like this in the control source:

    =DSum("Subtotal","qryOrders")

    My query (qryOrders) pulls in fields from 3 tables, 2 of which are "Price" and "Quantity". A calculated field, "Subtotal" is the product of the 2 fields and it works perfectly. This query is the record source for a report called rptInvoice. This too works fine. The problem arises when I try to sum the values in the Subtotal field of the query using a textbox in the report footer. I just get an error each time. I'm sure this ought to be quite straightforward but I'm making heavy weather of it.
    I hope I've not overelaborated but I thought the extra info may suggest to you the nature of the problem and/or a solution.

    Many thanks

    Rob
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

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

    Re: summing a field (A97)

    What is Total? Is it coming from the query? If not, then where?
    If you have named a control in the detail section to Total and tried to sum this, it wont work, you need to sum the query field.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

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

    Re: summing a field (A97)

    As long as Subtotal is a field in the underlying query of the report then =Sum(Subtotal) should work.
    You haven't named a control 'Subtotal' have you?
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  12. #12
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: summing a field (A97)

    Hi Pat

    That's what I am finding frustrating. There are no controls called Subtotal. Subtotal is the name of a field in the underlying query (qryOrders) from which the report is built. The report lists correctly the individual orders which include the Subtotal field.
    Still, it's all part of the learning curve.
    Further advise would be appreciated.

    Rob
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

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

    Re: summing a field (A97)

    This is a longshot, try renaming the field in the query to something else, not Subtotal.
    Failing all this, post your database, taking out any confidential info, and we can all have a look.

    I liked your original 'Alfred E' better!!

    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  14. #14
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: summing a field (A97)

    Pat
    No joy, I'm afraid.
    I'll delete both the query and report and reconstruct them anew. The db would be too big even when pared down.
    WRT Alf E, I found a Christmassy image, but my graphic manipulating skills are even worse than my access ones.

    Seasonal greetings to you and thanks for your help.

    Cheers

    Rob
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

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

    Re: summing a field (A97)

    Why don't you zip and email it to me? My email address is in my profile.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Page 1 of 2 12 LastLast

Posting Permissions

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