Totalling SubReports - have tried and tried

S

Sue Compelling

Hi

I've read a range of posts and still can't get my subtotalling to work. Pls
help.

My main report has groupings per Social Worker and the Detail is by student
name.

The student details are populated by 5 subreports (derived from 5 crosstab
queries) and presents information as below...

Qtr 1 Qtr 2 Qtr 3 Qtr 4
Daniel

Events $150 $292
Vouchers $15 $10
Courses $200 $200
Literacy $180
Counselling $200 $200

I now want to be able to:

1) subtotal each students' data
2) subtotal each social workers' data


The sub reports are called:

QryCostEachStEventsCross subreport
QryCostEachStVouchersCross subreport
QryCostEachStCoursesCross subreport
QryCostEachStCourseLitNumCross subreport
QryCostEachStCounsellingCross Subreport

TIA
 
D

Duane Hookom

I would try to ignore the subreports other then their record sources. Build
totals queries that calculate the values you need for your main report and
join them in so all of the numbers you need are in your main report's record
source. Assume your subreports are there only to display details and nothing
else.
 
S

Sue Compelling

Hi Duane

Thanks for this - has got me started on the right track.

I still have a problem though ... I've created the 5 separate totals
queries, have joined them into the source query for the main report and when
trying to total them they keep returning a blank result.

My expression is as follows:

Qtr 1: [qrycosteachstcounsellingcrosssum].[sumofquarter
1]+[qrycosteachstcoursescrosssum].[sumofquarter
1]+[qrycosteachstcourseslitnumcrosssum].[sumofquarter
1]+[qrycosteachsteventscrosssum].[sumofquarter
1]+[qrycosteachstvoucherscrosssum].[sumofquarter 1]

TIA

Sue
 
D

Duane Hookom

It isn't clear why you need all of the crosstabs or what their SQL views are.
When you need 5 of something, I have a tendency to think a table structure
might not be normalized.

Can you provide some information about your table structures and data as
well as how you wan this information reported?
 
S

Sue Compelling

Hi Duane

The problem with self taught DB designers ....

Anyway, this gets awfully complicated and I'm ok if you say ... this is too
hard and I can't resolve the issue. I would be ok zipping and sending my DB
if you needed? (it's only 1.5MB)

My DB is for an organisation that works with kids at risk and assigns social
workers to provide support, coaching, activities and development
opportunities.

My need is that I want to be able to subtotal all the costs for a student
(by quarter)
and also represent all the costs for the Social Worker (derived from the
student costs) by quarter.

Costs will be incurred by the organisation for a variety of reasons - ie the
students will:

Attend Counselling
Attend a course
Receive Lietracy coaching
Receive a Gift Voucher
Attend an event

Each of these scenarios have a range of information that needs to be
captured (costs being only one of them).

The costs PER STUDENT are also calculated differently insofar as:

Counselling may have multiple attendance, various dates, various costs
Gift Vouchers will be a single costs attributed to each student per voucher
Events have a bulk cost (which is then divided by the # of students attended)
Courses generally have a one off entrance fee
Literacy coaching has multiple costs, dates etc

My Tbl structures are as follows:

TblCounselling:
CounsellingID
StudentIDFK
CouncDate
CouncCost
CouncPart
CouncReasons
CouncOutcomes

TblCourseAttendance:
StudentIDFK
CourseTitleFK
CCommencementDate
CCompletedDate
CCosts

TblCourseLiteraryCosts
StudentIDFK
CourseTitle
AssessmentConducted
CourseStatus
CCommencmentDate
CCompletionDate

which is linked to the costs ...

TblCourseLiteraryCosts
StudentIDFFK
CostDate
CostValue

TblEvents:
EventID
EventType
EDescription
EDateStart
EDateFinish
EDuration
ERationale
EOutcomes
EStaffNos
EStaffPayment
EStaffSufficiency
ERAMS
EHASIE
EVehicles
EAttachments

which is linked to the costs ...

TbleEventCosts
EventIDFK
EPetrol
EHire
EFood
EMisc
EmiscDesription
ECode

TblVouchers:
VoucherID
VoucherType
VDescription
VDate
VRationale
VCosts
VAttachments

TIA
 
D

Duane Hookom

I would use a union query to normalize all the costs so you ended up with a
query with columns for SocialWorker, Student, CostType, CostDate, CostAmt.
You could then create your crosstab and use the basic sorting and grouping in
the report to create totals by SocialWorker and Student and whatever else.
 
S

Sue Compelling

Duane - this was brilliant advice. I had never done a union query before and
it was the perfect solution. Really appreciate your guidance.
 
Top