summing a field (A97)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

Oh well, it happens to all of us. What was it you were asking?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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,

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Have you solved your summing problem?
Pat

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Why don’t you zip and email it to me? My email address is in my profile.
Pat

Reply | Quote

Pat

I’ve managed to trim down the db. rptInvoice will ask for a parameter for Customer ID which is “1” (no quotes). I hope you can sort this out for me. I’m sure it must be something simple.

Cheers

Rob

Reply | Quote

Rob

I moved the subtotal from the Page header to the report footer and it worked.
Is there any reason you have it in the page header?

Pat

Reply | Quote

Pat
I’m looking at the report in design mode now and I see I have the text box in the page footer instead of the Report footer. However, on moving it there, the report changes to portrait from landscape. Once a report has been created, how do you change orientation?
Thanks for sorting the other problem out. I cannot believe I’m so unobservant.

BTW, Alf’s back.

Cheers Rob

Reply | Quote

Neuman I presume.

To change the orientation go into File/Page Setup/Page.

I sometimes look at things and can’t see them for looking at them.

Happy Xmas to you too !!

Pat

Reply | Quote

Pat
You are a Trojan!
Begging your indulgence, I have one more question which would make sleep easier over Chrimble.
After a period of time, I would want to assess the popularity of Dishes sold to improve the service provided to customers. The table, tblOrders stores the Dishes ordered over a period of time. What would be the query that returned the sum of each individual dish from tblOrders. Would a Crosstab be appropriate in this case?

Cheers

Rob

Reply | Quote

A crosstab souds good to me.
I have been accumulating race results (horses) over the last 18 months and have found crosstab queries to be extremely valuable.
Pat

Reply | Quote

Pat

Many thanks. I shall experiment.

Cheers

Rob

Reply | Quote