Report Calculation

[Azeez_Andaman]

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.

[June7]

Why does it have to be on the main report? Could use Grouping & Sorting with aggregate calcs in the subreport to generate the total.

[Azeez_Andaman]

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.

[June7]

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?

[Azeez_Andaman]

Due to net problem attachment failed. re-sending the same

[June7]

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.

[Azeez_Andaman]

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

[June7]

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.