PUT TOTAL AT THE END OF A COUNT QUERY

[Sinner]

Hi,


I have a table with items & their quantities.
I have a count query

field1 are items.
field2 is quantity.

When i count(i.e.query) I get items & total of quantities against them
in adjacent column.

In count query, how do I put the word "Total:" at the end of item list
& sum(quantityfield) under the quantity field.

Example:
Query should look like below.

field1 field2
a 10
b 12
c 15
Total: 37

Thx.

 

[Rob Parker]

The simple answer, in Access, is that you can't/don't do things like that.
Seems like you're trying to emulate a spreadsheet, and that's not what
Access is.

However, if you display the content of your query, which is giving you the
totals for each item, in either a form or a report (the standard method of
interfacing with query ouput in Access), you can do this quite simple by
putting an unbound textbox control into the form/report footer and setting
its Control Source to an expression:
=Sum([field2])

HTH,

Rob

 

[Sinner]

The simple answer, in Access, is that you can't/don't do things like that..
Seems like you're trying to emulate a spreadsheet, and that's not what
Access is.

However, if you display the content of your query, which is giving you the
totals for each item, in either a form or a report (the standard method of
interfacing with query ouput in Access), you can do this quite simple by
putting an unbound textbox control into the form/report footer and setting
its Control Source to an expression:
    =Sum([field2])

HTH,

Rob

Hi,

I have a table with items & their quantities.
I have a count query

field1 are items.
field2 is quantity.

When i count(i.e.query) I get items & total of quantities against them
in adjacent column.

In count query, how do I put the word "Total:" at the end of item list
& sum(quantityfield) under the quantity field.

Example:
Query should look like below.

field1   field2
a         10
b         12
c         15
Total:    37

Thx.- Hide quoted text -

- Show quoted text -

Well I have seen a tutorial on YOUTUBE regarding same in which he made
a sale report for some manager with a totals line at the bottom of a
count query.
I'm unable to find that now.

I think he made a seperate query just to display a total.
He also has a count query.
Then he had I guess a union of both & displayed.

Can you help in this context??

Thx.

 

[John Spencer]

You can do it in a query, however it makes more sense to do it as described by
Rob Parker and show the result in a report (or on a form).

This query will give you the desired result, BUT it will not be updatable so
except for displaying (or printing in a report) the query is not the best way
to solve the problem.

SELECT Items, Quantity, "Detail" as LineType
FROM YourTable
UNION ALL
SELECT "TOTAL:", Sum(Quantity), "Total" as LineType
FROM YourTable
ORDER BY LineType, Items


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Sinner wrote:

////// SNIP ///////