Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2008
    Posts
    15

    Unanswered: Help with MS Access database

    Hi,
    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.

    Dept DR810
    Admin 1000
    Admin 500
    MIAP 450
    MIAP 300
    Admin 400
    SystemDev 1000

    Total Admin 1900
    Total MIAP 700
    SystemDev 1000
    ----------------------------------------------------------
    Dept DR820
    Admin 2000
    Admin 800
    Admin 450
    MIAP 300
    Admin 400

    Total Admin 3650
    Total MIAP 400
    --------------------------------------------------------
    Grand Admin Total 5550
    Grand MIAP Total 1100
    Grand SystemDev 1000

    I don’t know how to do my totals – I have attached my database. The report in question is called ‘Sod budget’. The best way for you to understand it is to look at the report within the DB.

    Thanks in advance
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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

  3. #3
    Join Date
    Jan 2008
    Posts
    15

    Help with MS Access database

    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.

    Samy

    Dept DR810
    Expenditure Type £
    Admin 1000
    Admin 500
    MIAP 450
    MIAP 300
    Admin 400
    SystemDev 1000

    Total Admin 1900
    Total MIAP 700
    Total SystemDev 1000
    ----------------------------------------------------------
    Dept DR820
    Expenditure Type £
    Admin 2000
    Admin 800
    Admin 450
    MIAP 300
    Admin 400

    Total Admin 3650
    Total MIAP 400
    --------------------------------------------------------
    Grand Admin Total 5550
    Grand MIAP Total 1100
    Grand SystemDev Total 1000

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so
    either add another level of grouping
    or
    use sql aggregate functions or a domain lookup (using dlookup int he group footer on format code
    or
    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
    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
    in the forms on load event...
    Code:
    totAdmin =0 'zero our overall totals
    totMIAP= 0
    totDev = 0
    totunknown = 0
    in the group header event
    Code:
    grpAdmin =0 'zero our running totals
    grpMIAP =0
    grpDev = 0
    grpUnknwon = 0
    in the detail on format event

    Code:
    select case <expensetype>
    case "MIAP":  grpMIAP =grpMIAP +<amount>
    case "Admin": grpAdmin = grpAdmin  +<amount>
    Case "Dev":    grpDev = grpDev  +<amount>
    case else:      grpUnknown=grpunknown  +<amount>
    end select
    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
    Code:
    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
    Code:
    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

    HTH

  5. #5
    Join Date
    Jan 2008
    Posts
    15

    Help with Access database

    Thank you for all your help.
    I tried the code you suggested & it works.
    Brilliant.

    Samy5

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •