Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
total sum on a query
- Thread starter kevamb
- Start date
Kev,
You have to get the grouping thing down to deal with Summary queries. You're asking Access to return both invidividual values for each entry and return a total of all the values.
Two options:
A. Create a summary query where you show the field as a total, e.g.:
ID Field | Number field
group by Sum
save this as qry_TotofField etc. This query gives you an independent reference to the total which you can use in the next query in which you select from the previous query and the original table(also a summary)
ID Field | Number field | Tot:qry_Tot.[NumberField]
group by sum group by
This will show each item value and the total alongside it
B. Dump the table / select query into a report and have the sum appear at the bottom--use empty textbox with control source = sum([FieldName])(to do aggregate functions in a calculated text box they must be in either report header or footer (or Grouping footer if a sum of the group is sufficient.
Hope I understood your question correctly and that this helps.
FYI If you drag a text box from the field list first then try to turn it into a calculated text box you'll get "error?" when you've typed in a perfectly good formula (irritating Access quirk). For calculated texts always bring them empty from the toolbox.
You have to get the grouping thing down to deal with Summary queries. You're asking Access to return both invidividual values for each entry and return a total of all the values.
Two options:
A. Create a summary query where you show the field as a total, e.g.:
ID Field | Number field
group by Sum
save this as qry_TotofField etc. This query gives you an independent reference to the total which you can use in the next query in which you select from the previous query and the original table(also a summary)
ID Field | Number field | Tot:qry_Tot.[NumberField]
group by sum group by
This will show each item value and the total alongside it
B. Dump the table / select query into a report and have the sum appear at the bottom--use empty textbox with control source = sum([FieldName])(to do aggregate functions in a calculated text box they must be in either report header or footer (or Grouping footer if a sum of the group is sufficient.
Hope I understood your question correctly and that this helps.
FYI If you drag a text box from the field list first then try to turn it into a calculated text box you'll get "error?" when you've typed in a perfectly good formula (irritating Access quirk). For calculated texts always bring them empty from the toolbox.
- Status
Similar threads
- Locked
- Question
- Locked
- Question