Need Help printing Attendance Report

[connieharper]

I can't figure out how to print this report. It is an
attendance report for a particular month. There is one
record per day of attendance in the table. The user wants
to have columns across the top that indicate the day of
the month. The columns go from 1 - 31. The query should
read the attendance record and determine what day of the
month it is and then put that number in the appropriate
column on the report. Example: Looking at the report
layout below, Student #1 was present 3 hours on December
1 and was not present at all on December 2. Student 2 was
no present on Dec. 1 or 2 but was present on December 4
for 4 hours, etc. Using DATEPART I figured out how to
get the day of the month from the date field in the
attendance file but I can't figure out how to get the
records to print across the report for the whole month.

Here's an outline of the columns and heading:

July 2003
Day of Month: 1 2 3 4 5 6 7 8 9 10 etc.

Student 1 HRS: 3 0 2 3 2.5 0 1 0 0 4
Student 2 HRS: 0 0 1 4 1.5 0 0 6 1 0

Hrs Totals 3 0 3 7 4.0 0 1 6 1 4

 

[Marshall Barton]

I can't figure out how to print this report. It is an
attendance report for a particular month. There is one
record per day of attendance in the table. The user wants
to have columns across the top that indicate the day of
the month. The columns go from 1 - 31. The query should
read the attendance record and determine what day of the
month it is and then put that number in the appropriate
column on the report. Example: Looking at the report
layout below, Student #1 was present 3 hours on December
1 and was not present at all on December 2. Student 2 was
no present on Dec. 1 or 2 but was present on December 4
for 4 hours, etc. Using DATEPART I figured out how to
get the day of the month from the date field in the
attendance file but I can't figure out how to get the
records to print across the report for the whole month.

Here's an outline of the columns and heading:

July 2003
Day of Month: 1 2 3 4 5 6 7 8 9 10 etc.

Student 1 HRS: 3 0 2 3 2.5 0 1 0 0 4
Student 2 HRS: 0 0 1 4 1.5 0 0 6 1 0

Hrs Totals 3 0 3 7 4.0 0 1 6 1 4

I think you want to use a crosstab query for this. Be sure
to specify the query's Column Headings property with the
days of the month: 1,2, . . ., 31

 

[connieharper]

I have never done a crosstab query. I looked at the one
in Northwind and have a couple of questions. I have to
have the days of month 1 -31 across the top whether or
not there are values for all days in the record. If
someone was absent on a day, a zero will appear under
that day. In this crosstab query, do I add 31 different
entries in the design grid for each day and then select
column heading for each one? Then, how does the value
for each day appear under the right one? What would
those column entries look like in a crosstab query?

Thanks for your help.

 

[Marshall Barton]

I have never done a crosstab query. I looked at the one
in Northwind and have a couple of questions. I have to
have the days of month 1 -31 across the top whether or
not there are values for all days in the record. If
someone was absent on a day, a zero will appear under
that day. In this crosstab query, do I add 31 different
entries in the design grid for each day and then select
column heading for each one? Then, how does the value
for each day appear under the right one? What would
those column entries look like in a crosstab query?

A crosstab query shows the aggregate of the values under the
column heading value borken down into a row fro each group
by value. In other words the crosstab query wizard takes
care of most of it for you. Just tell it the column head is
the day of the month, row head is the student hrs and the
value is Sum.

To make sure you have all 31 days of the month, set the
query's ColumnHeadings property. This is different from
specifying the field you want as a column head. Make sure
you are displaying the query's Property sheet (View menu) by
clicking in a blank area of the upper part of the query
design window. The second property in the property list is
the Column Headings you need to enter the comma separated
days of the month.
--
Marsh
MVP [MS Access]