I wonder if anyone could help me.
I have created a report called ‘SoD budgets’ in access database (zipped attached database called test) which lists the expenditure per department.
Each department has different type of expenditure e.g. admin, MIAP, System Development … etc.
I would like to sum the expenditure type per dept & then arrive at Grand total per expenditure type for all departments as shown below e.g.
presumably you decided to use a wizard to design your report
so you have designed your report telling the report what table or query to use
then you are offered the choice of waht columns or fields to use
then you are offered the choice of what columns or fields to use to sort the report
then you allowed the report skeleton to be created
did a bit of prettifying the report
then you need to define a group and report footer, look for the 'sorting and grouping' button on the toolbar.
insert a group footer based on department ID
insert a text box into that group footer and set its data source to =sum([exptype])
..assuming that exptype is the name of the column or field in your table
you may want to add a label as well to identify say "total expenditure, this group"
the return to the form designer/layout, and make space in the report footer
copy the text box from the group footer and place it in the report footer
...and there y'go
Thanks for your response.
I tried what you suggested & the group footer total now adds up all expenditure for different types within each dept – i.e. it adds up admin / software development…etc expenditure which may occur within each department.
How would I show the total for each expenditure type within each dept separately & then the grand total for each expenditure type at the bottom of the report – as shown in the example below:
Is this done by VB coding within the report – I would be grateful of any help.
either add another level of grouping
use sql aggregate functions or a domain lookup (using dlookup int he group footer on format code
run you own totals in code
of the 3 I'd do the latter, but thats because code doesn't faze me, and its infinitely more flexible and customisable.
view the code
define your working variables at the head / top of the code
option explicit 'make sure all variables are defined
dim grpAdmin as single
dim grpMIAP as single
dim grpDev as single
dim grpUnknown as single
dim totAdmin as single
dim totMIAP as single
dim totDev as single
dim totUnknown as single
select case <expensetype>
case "MIAP": grpMIAP =grpMIAP +<amount>
case "Admin": grpAdmin = grpAdmin +<amount>
Case "Dev": grpDev = grpDev +<amount>
case else: grpUnknown=grpunknown +<amount>
define 3 /4 text boxes with associated labels in your group footer
leave the data / control source blank, call them something relevant and meaningful such as.... txtgrpMIAB,txtgrpDev... etc
in the group footer on format event, assign the values of your group totals to the text boxes on the report
txtgrpMIAB.text = format(grpMIAB,"£#,##0.00p")
txtgrpDEv.text = format(grpdev,"£#,##0.00p")...... etc
totMIAB=totMIAB+grpMIAB 'add the group total to the overall total
....and so on for the other running totals
in the report footer event
txttotMIAB.text = format(totMIAB,"£#,##0.00p")
txttotDEv.text = formatr(totdev,"£#,##0.00p")...... for the other 1 or 2 text boxes
so have a look at the report in design mode
view the code
have a play