Results 1 to 8 of 8
  1. #1
    Azeez_Andaman is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2011
    Location
    Port Blair, India
    Posts
    40

    Question Report Calculation

    i have created a report with a sub-report in it. main report display data from a table which contain information. sub-report display information based on the contents of the main report. every thing is running fine. but the problem is that i could not bring the totals of sub-form in main form. what should i do to display total in main form. the report contain the following details.


    MAIN REPORT: Fields contain : code, name, place, income source, Age Group
    SUB REPORT: Fields contain : code, Age Group, Quarters, Source 1, Source 2, Source 3, Source 4. (Source 1,2,3,4 ... are income source)
    sub report open on the basis of Code and Age Group of main report.
    i want to display the grand total in the main form's Footer section.
    please help me to bring the grand total on main form.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,697
    Why does it have to be on the main report? Could use Grouping & Sorting with aggregate calcs in the subreport to generate the total.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Azeez_Andaman is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2011
    Location
    Port Blair, India
    Posts
    40
    i could not follow the above suggestion. i am sending my original report. please look into it and suggest me an idea to bring the total on the main report.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,697
    Nothing was attached to your post.

    Access Help has guidelines on building reports using Grouping & Sorting features with summary calculations. Search Help for 'create a grouped or summary report'. This is basic Access functionality in designing reports.

    BTW, you refer to both reports and forms in your post. Which object type are you building?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Azeez_Andaman is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2011
    Location
    Port Blair, India
    Posts
    40
    Due to net problem attachment failed. re-sending the same

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,697
    Seeing is understanding! My earlier suggestion won't work. I don't do many aggregate data reports so was 'shooting from the hip' on that one and had to do some learning with your project. If you didn't have the Particulars data at the left side of report, all of this could be in one report, no subreport. The only way I can see to make this work is to have both main report and subreport have as RecordSource a query that joins the 2 tables. Then the main query would be a GROUP BY with aggregate Sums. Then the Grand Total textboxes in the main report footer would sum the sums: Sum(SumOfDaLda). Both queries would need the same filter criteria.

    Eliminate the ReportFooter_Format code.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Azeez_Andaman is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2011
    Location
    Port Blair, India
    Posts
    40
    Hello June

    Sorry personal question. Your name is shown as June7. is it your birth date?

    Any how, i could not build the query as suggested above. * is not working while summing. can you provide a sample in my mdb itself and send me back. i will try with that.

    thank you

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,697
    Cannot use wildcard in GROUP BY query. The main form RecordSource would be:
    SELECT DaTbl1.DaCode, DaTbl1.DaName, DaTbl1.DaRange, DaTbl1.DaBasic, DaTbl1.DaGrade, DaTbl1.DaDesgn, DaTbl1.DaSex, Sum(DaTbl2.DaLbp) AS SumOfDaLbp, Sum(DaTbl2.DaLgp) AS SumOfDaLgp, Sum(DaTbl2.DaLda) AS SumOfDaLda, Sum(DaTbl2.DaLsca) AS SumOfDaLsca, Sum(DaTbl2.DaLta) AS SumOfDaLta, Sum(DaTbl2.DaLwa) AS SumOfDaLwa, Sum(DaTbl2.DaLtot) AS SumOfDaLtot, Sum(DaTbl2.DaRda) AS SumOfDaRda, Sum(DaTbl2.DaRsca) AS SumOfDaRsca, Sum(DaTbl2.DaRta) AS SumOfDaRta, Sum(DaTbl2.DaRwa) AS SumOfDaRwa, Sum(DaTbl2.DaRtot) AS SumOfDaRtot, Sum(DaTbl2.DaNet) AS SumOfDaNet, Sum(DaTbl2.DaNPS) AS SumOfDaNPS, Sum(DaTbl2.DaBal) AS SumOfDaBal
    FROM DaTbl2 RIGHT JOIN DaTbl1 ON DaTbl2.DaCode = DaTbl1.DaCode
    GROUP BY DaTbl1.DaCode, DaTbl1.DaName, DaTbl1.DaRange, DaTbl1.DaBasic, DaTbl1.DaGrade, DaTbl1.DaDesgn, DaTbl1.DaSex, DaTbl1.DaGroup, DaTbl1.DaFrDt, DaTbl1.DaToDt, DaTbl1.DaNPS, DaTbl1.DaPNP, DaTbl1.DaSet
    HAVING (((DaTbl1.DaRange)='Head Office') AND ((DaTbl1.DaGroup)='C') AND ((DaTbl1.DaFrDt) Between #6/1/2011# And #12/31/2011#) AND ((DaTbl1.DaToDt) Between #6/1/2011# And #12/31/2011#) AND ((DaTbl1.DaNPS)=False) AND ((DaTbl1.DaPNP)=True) AND ((DaTbl1.DaSet)=5))
    ORDER BY Sum(DaTbl1.DaID);

    The subform RecordSource would be:
    SELECT DaTbl2.*
    FROM DaTbl2 RIGHT JOIN DaTbl1 ON DaTbl2.DaCode = DaTbl1.DaCode
    WHERE (((DaTbl1.DaGroup)='C') AND ((DaTbl1.DaFrDt) Between #6/1/2011# And #12/31/2011#) AND ((DaTbl1.DaToDt) Between #6/1/2011# And #12/31/2011#) AND ((DaTbl1.DaNPS)=False) AND ((DaTbl1.DaPNP)=True) AND ((DaTbl1.DaSet)=5))
    ORDER BY DaTbl1.DaID;

    Birthdate is December, middle name is June.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Calculation in a report
    By jlzags in forum Access
    Replies: 3
    Last Post: 08-25-2011, 01:47 PM
  2. Query or Report Calculation
    By huber57 in forum Access
    Replies: 0
    Last Post: 03-27-2011, 11:33 AM
  3. lookup value for report calculation
    By josbor01 in forum Reports
    Replies: 0
    Last Post: 03-02-2010, 01:41 PM
  4. report average calculation
    By ZipDoc in forum Reports
    Replies: 1
    Last Post: 01-28-2010, 09:08 PM
  5. Report Calculation
    By thestclair in forum Reports
    Replies: 2
    Last Post: 03-28-2006, 12:23 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums