how to extract totals based on a criteria

[eclat73]

Hi am a newbie. My database is single table. Each record has a bill no , a date , several accounting heads from which relevant heads for one record have some figures, a text field which indicates the status of the bill. The data pertains to last 9 years and is updated daily. I need to extract information on year to year basis and cumulative as on date as to what is the total amounts in the bill under various sub heads and overall. The fiscal year is 01 apr to 31 mar of next year. I know how to get the total of a column after limiting my query to a range of date. However I am not able to figure out to use this total thereafter.


Basically I am looking at a output which is two columns one listing each year and the next one having corresponding totals either for a particular subhead or all together

[orange]

Please show us what you have tried. SQL of query(ies).

[eclat73]

So I have a query which asks me for the start date and end date. Using this as parameters in the date field I limit the records to one fiscal year . Then in the datasheet view I add the totals row and choose the sum option in the rquired field (s). So I can run the query once for each year and note the result in the totals row. However I want to generate this output of totals matched to the year in one place

[June7]

"output which is two columns one listing each year and the next one having corresponding totals either for a particular subhead or all together"

Makes me think you want an aggregate query with or without filter for subhead.

Maybe build a report and use Grouping & Sorting features with aggregate calcs in group header/footer sections