Adding values from sub reports in the main report

[peterk]

My problem is how to total values from sub reports in a control in the main
report.

I have the following items in Access 2003...

Query1 based on Table1 and Table2. Query1 includes an integer field named
Lessons1.

Query2 based on Table1 and Table2 (a different record set). Query2 includes
an integer field named Lessons2.

Report1 based on Query1, with a control Name Text1 and control source
Lessons1.

Report2 (Query2 / Text2 / Lessons2 as above)

Report3 which has the following controls in the detail section:
StudentID
Name
Report1 and Report2 (as sub reports)

The result I want is like this:

ID Name Lessons1 Lessons2 Total
1 Smith 3 2 5
2 Jones 5 0 5
3 Brown 2 4 6

Everything works as I want it except for the Total column. I get a #Name?
error so I presume I'm not describing my controls correctly.

I have tried other approaches such as putting both fields in the same query,
but couldn't work out a way to do that either. The final result will involve
more queries / subreports but I want to get the concept right b4 I go too far.

 

[Marshall Barton]

My problem is how to total values from sub reports in a control in the main
report.

I have the following items in Access 2003...

Query1 based on Table1 and Table2. Query1 includes an integer field named
Lessons1.

Query2 based on Table1 and Table2 (a different record set). Query2 includes
an integer field named Lessons2.

Report1 based on Query1, with a control Name Text1 and control source
Lessons1.

Report2 (Query2 / Text2 / Lessons2 as above)

Report3 which has the following controls in the detail section:
StudentID
Name
Report1 and Report2 (as sub reports)

The result I want is like this:

ID Name Lessons1 Lessons2 Total
1 Smith 3 2 5
2 Jones 5 0 5
3 Brown 2 4 6

Everything works as I want it except for the Total column. I get a #Name?
error so I presume I'm not describing my controls correctly.

I have tried other approaches such as putting both fields in the same query,
but couldn't work out a way to do that either. The final result will involve
more queries / subreports but I want to get the concept right b4 I go too far.

I don't think that's a reaonable approach. The totals would
require a third subreport based on a query that combines the
other two queries. You can get the desired result by
starting off with the combined query as the basis for the
report without using any subreports.

Presumably(?), the data in your main report is linked to
tables 1 and 2 on the studentID field. Then you join the
tables on that related field and just include the fields
from all three(?) tables. If you will explain exactly what
fields are in which tables, I might be able to help you
construct the query.

 

[peterk]

ok, trusting your judgment...

The overall report I'm aiming for is by analogy with an aged debtors ledger.
Guitar students have lessons, pay fees (often in advance), etc. The report
would have these columns:

Student
Balance b/f
Lessons held (3rd mth ago)
Lessons paid (3rd mth ago)
Lessons held (2nd mth ago)
Lessons paid (2nd mth ago)
Lessons held (last mth)
Lessons paid (last mth)
Balance c/f

The tables and relevant fields are:

Table1 = Students
Fields = StudentID, StudentName, etc

Table2 = StudentLessons (sub table of Table1)
(This table has the data for each student lesson - Lessons are usually
weekly so a student will normally have 3 to 5 records for a given month)
Fields = LessonDate, intLessonsHeld (0 if the lesson is credited say for
sickness, 1 for normal 1/2 hr lesson, 2 for 1 hr lesson, etc) plus other
fields

Table3 = StudentPayments (sub table of Table1)
Fields = PaymentDate, intLessonsPaid (fee rates vary, so this field
identifies for a given payment how many lessons the student is entitled to)
plus other fields.

Over to you. I appreciate any help you can give.

 

[Marshall Barton]

The overall report I'm aiming for is by analogy with an aged debtors ledger.
Guitar students have lessons, pay fees (often in advance), etc. The report
would have these columns:

Student
Balance b/f
Lessons held (3rd mth ago)
Lessons paid (3rd mth ago)
Lessons held (2nd mth ago)
Lessons paid (2nd mth ago)
Lessons held (last mth)
Lessons paid (last mth)
Balance c/f

The tables and relevant fields are:

Table1 = Students
Fields = StudentID, StudentName, etc

Table2 = StudentLessons (sub table of Table1)
(This table has the data for each student lesson - Lessons are usually
weekly so a student will normally have 3 to 5 records for a given month)
Fields = LessonDate, intLessonsHeld (0 if the lesson is credited say for
sickness, 1 for normal 1/2 hr lesson, 2 for 1 hr lesson, etc) plus other
fields

Table3 = StudentPayments (sub table of Table1)
Fields = PaymentDate, intLessonsPaid (fee rates vary, so this field
identifies for a given payment how many lessons the student is entitled to)
plus other fields.

This now looks like a different report than what I thought
you were describing in your original post. I don't see any
way (other than in your mind) to link a payment to a lesson
so there is no way I can see to get lessons and payments
side by side. I don't even see how you can get an advance
payment for a dozen lessons from four months ago to appear
in a report that goes back three months. Seems like some
months may not have any payments??

The rest of this is my guess at a way to get the recent data
in the same report even if the payments are not tied to the
lessons.

Presumably, StudentLessons and StudentPayments also have a
StudentID field so the tables can be linked.

Try creating a query like:

SELECT Students.StudentID,
Students.StudentName,
"Lesson" As TransType
StudentLessons.LessonDate As TransDate,
StudentLessons.intLessonsHeld As TransAmt
FROM Students INNER JOIN StudentLessons
ON Students.StudentID = StudentLessons.StudentID
UNION ALL
SELECT Students.StudentID,
Students.StudentName,
"Payment" As TransType
StudentPayments.PaymentDate,
StudentPayments.intLessonsPaid
FROM Students INNER JOIN StudentPayments
ON Students.StudentID = StudentPayments.StudentID

Then the report needs to group as follows:
StudentName with header
TransType with header and footer
TransDate without header or footer

The Student name header would have the student specific
fields.

The TransType header would just have the TransType field and
the footer can then total the TransAmt field to get the
total for lessons or payments.

If you don't like that kind of arrangement, then I guess
your original report with subreports is a good arrangement.
However, you can not line up payments with lessons using the
data you have in the tables so your question about getting a
row total just doesn't make sense to me.

 

[peterk]

Wow! Very interesting. I have followed your example and the results look
very promising. Further comments / questions:

1st, as I typed the lines in, I flicked between SQL and design views to see
what was happening. By the end, I couldn't use design view. Is this a
different kind of query (I've only ever worked in design view queries). If
so, can you point me to an article so I can learn more pls?

2nd, I have totals for the "lessons" and for "Payments" TransTypes. But the
overall total needs to be Payments minus Lessons. How do I achieve that?

3rd, and more generally, your assumptions were pretty good. I'm sorry if I
didn't explain clearly in the first posting. As a grateful user of this
community resource, I'm aware of the big commitment others make and want to
do as much of the work as I can myself. I aimed to just ask about the part
that I thought I needed the answer to.

Giving you an overview - The database is managing a music teaching business.
The students all pay up front, so no debtors. However, they have two
choices - pay lesson by lesson, or pay for 10 lessons in advance, at a
reduced rate. About 3 quarters of students choose this option. As lessons
are held, they are credited against that student's balance. Details of this
can vary (eg, a student cancels lessons for 3 wks because he's away on
business; another misses a lesson without notice: it is billed but she is
entitled to a makeup lesson.)

Both the Lessons and Payments tables are linked to the Student table by the
StudentID field, but not to each other. I don't need to know that lesson A
relates to payment C. What I really need to know is the running balance at
any point.

So what you've given is effectively a detailed history for each student -
all the lessons and all the payments. I had that, but in a messy form, and
will probably use your better approach to tidy up.

What I don't have, and have been working on, is a summary page - one line
per student, that gives the number of lessons that each student has in
credit. In practice, students ask for an update every couple of months,
which is why I suggested a 3 month period (some of the students have been
having lessons for 2-3 years, and their detailed history would be pages
long). I would like to be able to say at a glance: "In January, you were 2
lessons in credit. Since then you've paid for 10 lessons, you were billed 4
lessons in February, 2 in March - so you are entitled to 6 more lessons
before you have to pay again."

hth. Any suggestions? As always, I'm grateful.

 

[peterk]

Letting you know my progress so far:

I've added this field to your union query:

IIf(12*(Year(Date())-Year([dtLessonDate]))+Month(Date())-Month([dtLessonDate])>3,4,12*(Year(Date())-Year([dtLessonDate]))+Month(Date())-Month([dtLessonDate])) AS TransTag

There may be a tidier way to do it, but this gives me a way of grouping the
records by month back for a few months (3 in this case) then all records
before that have a Tag value of 4 and can be grouped together.

Still need help with the totals for the summary page...
And in the detailed history, I would want to show the individual records for
the last 3 months, but just the b/fwd balance of records before that.

Cheers,

 

[Marshall Barton]

Wow! Very interesting. I have followed your example and the results look
very promising. Further comments / questions:

1st, as I typed the lines in, I flicked between SQL and design views to see
what was happening. By the end, I couldn't use design view. Is this a
different kind of query (I've only ever worked in design view queries). If
so, can you point me to an article so I can learn more pls?

That's right, a UNION query can not be done in the query
designer. There are lots of other things, especially Joins
that are not just simple equal comparisons in the ON clause.

2nd, I have totals for the "lessons" and for "Payments" TransTypes. But the
overall total needs to be Payments minus Lessons. How do I achieve that?

Either use some poor practice code in the TransType group
footer's Format event to save the lessons total in a hidden
text box:
If Me.TransType = "Lesson" Then
Me.txtLessonTotal = Me.txtTotalTransAmt
Else
Me.txtPaymentTotal = Me.txtTotalTransAmt
End If
Then the person footer can use those two value to get the
remaining balance.

The more I think about it, the more I don't like that idea.
Maybe you would be better off without the TransType grouping
and list the payments and lessons in one list sorted by
date. You could then have a running balance *using a
RunningSum text box).

On the other hand, your original subreport approach may be
the best way to present the two type of transactions. You
can not get a "row total across two unrelate sets of
records, but you can get the total by using a simpel Sum
text box in each subreport's footer section. THe main
report can then use those totals in its own calculations by
using a text box expression likeL
=IIf(Paymentsubreport.Report.HasData,
Paymentsubreport.Report.txttotal, 0) -
IIf(lessonsubreport.Report.HasData,
lessonsubreport.Report.txttotal, 0)

3rd, and more generally, your assumptions were pretty good. I'm sorry if I
didn't explain clearly in the first posting. As a grateful user of this
community resource, I'm aware of the big commitment others make and want to
do as much of the work as I can myself. I aimed to just ask about the part
that I thought I needed the answer to.

Giving you an overview - The database is managing a music teaching business.
The students all pay up front, so no debtors. However, they have two
choices - pay lesson by lesson, or pay for 10 lessons in advance, at a
reduced rate. About 3 quarters of students choose this option. As lessons
are held, they are credited against that student's balance. Details of this
can vary (eg, a student cancels lessons for 3 wks because he's away on
business; another misses a lesson without notice: it is billed but she is
entitled to a makeup lesson.)

Both the Lessons and Payments tables are linked to the Student table by the
StudentID field, but not to each other. I don't need to know that lesson A
relates to payment C. What I really need to know is the running balance at
any point.

So what you've given is effectively a detailed history for each student -
all the lessons and all the payments. I had that, but in a messy form, and
will probably use your better approach to tidy up.

What I don't have, and have been working on, is a summary page - one line
per student, that gives the number of lessons that each student has in
credit. In practice, students ask for an update every couple of months,
which is why I suggested a 3 month period (some of the students have been
having lessons for 2-3 years, and their detailed history would be pages
long). I would like to be able to say at a glance: "In January, you were 2
lessons in credit. Since then you've paid for 10 lessons, you were billed 4
lessons in February, 2 in March - so you are entitled to 6 more lessons
before you have to pay again."

The summay is probably best dealt with by creating a
separate subreport based on a Totals type query that
calculates all the needed totals.

 

[Marshall Barton]

Letting you know my progress so far:

I've added this field to your union query:

IIf(12*(Year(Date())-Year([dtLessonDate]))+Month(Date())-Month([dtLessonDate])>3,4,12*(Year(Date())-Year([dtLessonDate]))+Month(Date())-Month([dtLessonDate])) AS TransTag

There may be a tidier way to do it, but this gives me a way of grouping the
records by month back for a few months (3 in this case) then all records
before that have a Tag value of 4 and can be grouped together.

Can't you get the same thing by using:
DateDiff("m", dtLessonDate, Date())

Still need help with the totals for the summary page...
And in the detailed history, I would want to show the individual records for
the last 3 months, but just the b/fwd balance of records before that.

You can get the balance prior to a date by using a text box
expressions like:
=DSum("amount", "Payments", "dtPayment<DateAdd('m', -3,
Date()")

 

[peterk]

PeterK wrote
See below...

Letting you know my progress so far:

I've added this field to your union query:

IIf(12*(Year(Date())-Year([dtLessonDate]))+Month(Date())-Month([dtLessonDate])>3,4,12*(Year(Date())-Year([dtLessonDate]))+Month(Date())-Month([dtLessonDate])) AS TransTag

There may be a tidier way to do it, but this gives me a way of grouping the
records by month back for a few months (3 in this case) then all records
before that have a Tag value of 4 and can be grouped together.

Can't you get the same thing by using:
DateDiff("m", dtLessonDate, Date())

Well I did tell you there was a tidier way to do it! I didn't click till
this post that the DateDiff function could be used with a month interval.

I've tried the DSum function and got it to work correctly in one setting, so
I think I'm on the way. Will let you know if I get stuck on any details but
hopefully not. I really appreciate all the time you give to me and others on
this site.

Thanks, Peter.