Totals Query (Grand Total)

[Bomac]

Hello All,

Running WinXP and Access 2003.

I have to admit to not being a pro user of Access and that at the moment I’m feeling particularly stupid (and fed up !) All afternoon I have been trying to create a simple query using the query grid (I don’t do code) to do a straight forward aggregation of a currency field.

Only one table is being interrogated and it records money going into and out of a specific account. Monies paid out include things like rent and plant hire. Regular and irregular amounts. All I want the query to do is list all the transactions recorded in the table within dates “Entered” to the query by the user. This much I have managed. The bit I can’t do, is show in an extra column, the single Grand Total of all payments made – regular and irregular – makes no odds. Using the Sigma facility and the ‘sum’ parameter all I have managed to produce is a duplicate list of what is already shown under the field by the query.

I’m sure I have built such a query in the past but I can’t remember where or how. I’ve searched all my manuals and had a prowl on the net but haven’t managed to find what I need.

I would very much appreciate some help or guidance.

Thank you

Bomac.

 

[Brianwarnock]

The bit I can’t do, is show in an extra column, the single Grand Total of all payments made –

That's true you can't in one query list records and produce a Grand Total of them. Its what reports are for.

Brian

 

[vbaInet]

... or perhaps a Pivot Table.

 

[Bomac]

Thank you Brian. I will try to remember that in future. My stupidity shows in that I was trying to do something which, from experience, I should have known was not possible. As I said I am not a professional user just an oldie trying to help a small local business that's finding things a bit tough.
Thank you also vbaInet. I shall certainly look into using a pivot table before I consider a change in procedures.
I'm grateful to you both for putting my mind at ease

Regards,

Bomac

 

[Angela9]

I am trying to do an alias column with hours worked. I am stumped.Calculate the # of hours worked for ea. tech YTD Earnings/Rate. Assign alias-Hours Worked as an integer. Someone please tell me how to do this!

 

[johnywhy]

you can't in one query list records and produce a Grand Total of them. Its what reports are for.

Not quite true, you can show Grand Totals in a query.
-Open the query in datasheet view.
-Click the Home ribbon
-Click the Totals button
-At the bottom of the datasheet window, you see the Totals cells.
-Click the dropdown on any Totals cell, and pick the kind of grand total you desire.

Works in 2010, not sure about other versions.