manipulating totals in report

[zapspan]

Hi.
I use an access database to keep track of student attendance at a language
lab. Each record is an "attendance event". At the end of the semester, I
print out a report, organized by professor and then by student, of hours of
lab attendance accumulated during the semester, using the running total
option.

However, I would like to do some potentially complicated manipulations of
the data, and I'm not sure in which component of access to do this and how to
do this. What I'd like to do is the following:
1) if a given student has attended language lab for more than 2 hours in a
day, then the total for that day should only be 2 hours [they can't earn more
than 2 hours in a day]. I have already figured out to make sure that any one
lab attendance event can only be a maximum of two hours long, but I don't
know what to do if a student has visited the lab more than once in a day,
since then it's the sum of all the visits for that day that can't be more
than 2 hours.

2) one particular professor disallows his students from earning more than 2
hours per week, so I would like to calculate weekly totals for students for
that class and reduce any total over 2 to just 2 - I imagine that since the
other professors don't have this restriction, I would have to calculate two
totals for each student - the total not reflecting the 2-hr weekly max, and
the total reflecting the 2-hr weekly max, although if I could avoid this,
that would be great

3) I want to print out individual reports for each professor, rather than
just printing out one huge report and then have to cut pages with scissors.

Any help on any or all of these three areas would be greatly appreciated.

Thanks,
Mike

 

[Damian S]

Hi Mike,

For (1) and (2) you probably will need to do most of the hard work in
queries, then use those queries to power your report. eg: In Query1, select
the student ID, Day, sum of hours etc, but check for if the sum is more than
2, and if it is, use 2. Exclude the course from the professor in (2). Then,
create Query2 that selects the studentID, Week (or select a token day to
represent a week), sum of hours etc checking for the total for the week being
more than 2, using 2 if it is. Next, create Query3 that unions the results
of Query1 and Query2, and hey presto you have your set of data. Use Query3
to power your report.

For (3) below, either have your Professor Name as a group header and set it
to commence new page before header, or have a combo box to select the
Individual Professor (on a form) and use that to restrict to a single
professor.

Hope this helps.

Damian.

 

[zapspan]

Dear Damian.

Thank you so much for taking the time to answer me. I really set out to
follow your suggestions, but I find that my knowledge about Access is quite
limited. For example, I've never even used queries before, although I spent
several hours a couple of days ago, researching and experimenting.

Maybe you can answer a couple of specific questions. First let me clarify:
In the original table that I'm querying on, I have student, professor, time
in, time out, and date. You mention that I should query on student ID, day,
sum of hours, etc., but I don't yet have sum of hours. I think I found a way
to calculate the hours for a particular visit, so that each line in the query
contains a time lapse. However, how do I take all of the lines in that query
pertaining to a particular student for a particular day and convert that to a
single line that shows a daily total? Do I need to do a query on the query?
Likewise, what about query2 that you describe below - how do I tabulate all
of the attendance events for one student for a week into a single total? If
this is too much to explain, it's fine - just thought I'd ask.

Also, I had another problem. When I tried to insert values from a query
into the report, the report somehow acted as though I were inserting new
variables, not values to be drawn from an existing query, even though I
inserted the values from a dialog box that let me select the query and then
values from that query.

Thanks for your previous input and any addition input you may have time to
offer.

Sincerely,
Mike

Hi Mike,

For (1) and (2) you probably will need to do most of the hard work in
queries, then use those queries to power your report. eg: In Query1, select
the student ID, Day, sum of hours etc, but check for if the sum is more than
2, and if it is, use 2. Exclude the course from the professor in (2). Then,
create Query2 that selects the studentID, Week (or select a token day to
represent a week), sum of hours etc checking for the total for the week being
more than 2, using 2 if it is. Next, create Query3 that unions the results
of Query1 and Query2, and hey presto you have your set of data. Use Query3
to power your report.

For (3) below, either have your Professor Name as a group header and set it
to commence new page before header, or have a combo box to select the
Individual Professor (on a form) and use that to restrict to a single
professor.

Hope this helps.

Damian.

Hi.
I use an access database to keep track of student attendance at a language
lab. Each record is an "attendance event". At the end of the semester, I
print out a report, organized by professor and then by student, of hours of
lab attendance accumulated during the semester, using the running total
option.

However, I would like to do some potentially complicated manipulations of
the data, and I'm not sure in which component of access to do this and how to
do this. What I'd like to do is the following:
1) if a given student has attended language lab for more than 2 hours in a
day, then the total for that day should only be 2 hours [they can't earn more
than 2 hours in a day]. I have already figured out to make sure that any one
lab attendance event can only be a maximum of two hours long, but I don't
know what to do if a student has visited the lab more than once in a day,
since then it's the sum of all the visits for that day that can't be more
than 2 hours.

2) one particular professor disallows his students from earning more than 2
hours per week, so I would like to calculate weekly totals for students for
that class and reduce any total over 2 to just 2 - I imagine that since the
other professors don't have this restriction, I would have to calculate two
totals for each student - the total not reflecting the 2-hr weekly max, and
the total reflecting the 2-hr weekly max, although if I could avoid this,
that would be great

3) I want to print out individual reports for each professor, rather than
just printing out one huge report and then have to cut pages with scissors.

Any help on any or all of these three areas would be greatly appreciated.

Thanks,
Mike