How to create sub totals please. (1 Viewer)

[thebigman]

Hi
I am doing a basic cash ledger in a data sheet. I wondered how I can create a trial balance eg. Total all records that field equals subscriptions and vehicles and so on. Something like sumif.
Thank you for any help.

 

[theDBguy]

Just curious, would DSum() work?

 

[conception_native_0123]

You can produce totals in a query but I believe you have to group by other fields so that probably wouldn't work for you. Dsum might work because it has a where clause in it but be aware that it produces an answer in every row of the column

 

[thebigman]

Ok..I have about 10 totals to do. Haven’t used dsum before will have a look. It’s a pity excel does it with a formula.

 

[Gasman]

Group by query perhaps?

 

[Pat Hartman]

Sounds like you need a subform in the group footer that does a recap. Use the group by query suggested by Gasman but display it in a subform.

 

[CJ_London]

thing to remember is access is not excel. With excel you can refer to a cell on the row above, or sum a number of rows fairly easily - but then you can't sort the data without messing this up. This is because excel allows data, calculation and presentation to go hand in hand in the same view. With Access you have tables for data and queries, forms and reports for calculation and presentation.

For a trial balance, you just need a group by query. Gets more complex if you have years of data as you only really require the data for the last 12 months - which of necessity needs to include balance sheet opening balances.

If you are talking about a transaction summary, you would display the transactions in the detail section of a report and the total in a group footer

if you are running a simple cashbook - depends how the data is stored, but assuming it is normalised then again a group by query will do the job. Query might look something like

SELECT AccountName, Sum(Amount)
FROM myTable
GROUP BY AccountName