Attendance report and cross-tab query

[Guest]

BACKGROUND

I wrote a database that tracks weekly attendance by person. Tables are:

People = details on individuals
Attendance = tracks date and whether they attended on that date. All
records get either a true or false value.
Date = holds the date of the first Sunday of the quarter, this is changed
quarterly by the user.

The people and attendace tables are linked.

I created a report that lists people on the left and has blank squares on
the right with column heading showing a date for each Sunday in the quarter.
The first date is a dlookup of the Date table and pulls in the date of the
first Sunday in the quarter.

The report is printed and given to each class leader, they checkoff on the
report who attended that day, return the report to the office where the data
is entered into the database.

The blank report looks something like this:

Name WK1 WK2 WK3
John Smith [] [] []
Ed Jones [] [] []

I would like the report to show who attended by week as the quarter
progresses. So for week 1, the blank forms are passed out, attendance is
recorded and the forms returned to the office. The attendance is entered
into the database, then the forms are reprinted for next week and they now
show who attended the previous week. So the report for the second week would
look like this:

Name WK1 WK2 WK3
John Smith X [] []
Ed Jones [] []
(John was present and Ed was absent)

I tried a cross-tab query with NAME as the row heading and DATE the column
heading. The created a tabular report based on that query. It worked OK;
however the date fields in the query are the actual dates. I entered data
for the week of Sep 3 and Sep 10. There are now two date fields -- 9/3/2006
and 9/10/2006. When I add attendance for the next week, the new date is
added to the query, but the new date field isn't automatically added in the
report. So for each week, I would have to edit the report and add that date
field.

Any ideas on how to make this work? Is the cross-tab query the way to go or
is there a better way?

Thank you,
Kelvin

 

[Guest]

The solution is not simple, but it can be done. Do a google on dynamic
crosstab report. There are a number of good articles on how to do it.

Barry

 

[Guest]

Thank you, I have some reading to do.

Kelvin

The solution is not simple, but it can be done. Do a google on dynamic
crosstab report. There are a number of good articles on how to do it.

Barry

BACKGROUND

I wrote a database that tracks weekly attendance by person. Tables are:

People = details on individuals
Attendance = tracks date and whether they attended on that date. All
records get either a true or false value.
Date = holds the date of the first Sunday of the quarter, this is changed
quarterly by the user.

The people and attendace tables are linked.

I created a report that lists people on the left and has blank squares on
the right with column heading showing a date for each Sunday in the quarter.
The first date is a dlookup of the Date table and pulls in the date of the
first Sunday in the quarter.

The report is printed and given to each class leader, they checkoff on the
report who attended that day, return the report to the office where the data
is entered into the database.

The blank report looks something like this:

Name WK1 WK2 WK3
John Smith [] [] []
Ed Jones [] [] []

I would like the report to show who attended by week as the quarter
progresses. So for week 1, the blank forms are passed out, attendance is
recorded and the forms returned to the office. The attendance is entered
into the database, then the forms are reprinted for next week and they now
show who attended the previous week. So the report for the second week would
look like this:

Name WK1 WK2 WK3
John Smith X [] []
Ed Jones [] []
(John was present and Ed was absent)

I tried a cross-tab query with NAME as the row heading and DATE the column
heading. The created a tabular report based on that query. It worked OK;
however the date fields in the query are the actual dates. I entered data
for the week of Sep 3 and Sep 10. There are now two date fields -- 9/3/2006
and 9/10/2006. When I add attendance for the next week, the new date is
added to the query, but the new date field isn't automatically added in the
report. So for each week, I would have to edit the report and add that date
field.

Any ideas on how to make this work? Is the cross-tab query the way to go or
is there a better way?

Thank you,
Kelvin

 

[Duane Hookom]

There is an article on how to do this with monthly headings at
http://www.access.hookom.net/Reports.htm. You should be able to convert to
weekly or whatever data interval.