[Access 97] Printing Reports using Cross Tab Query and more!

[accessdummy]

Hi there,

I have a question to ask with regards to the mentioned subject.

Scenerio:
I've a sample database with 3 tables and 2 queries.

3 tables

masterlist tbl - contains information of a school of students consisting different grades (primary 1 - 6, secondary 1- 5)

grade tbl - I associate the grade type with a unique ID and then I use the ID to represent in the masterlist tbl. (e.g: 1001 - primary 1, 1002 - primary 2, 1003 - primary 3 etc)

attendance tbl - I associate the attendance type with a unique ID and then I use the ID to represent in the masterlist tbl. (e.g: 2001 - absence, 2002 - present, 2003 - Sick leave etc)

2 queries

Qryattendance - to extract information from masterlist tbl, attendance tbl and grade tbl.

Qryattendance_tab - to do up a crosstab qry for data analysing. row header consists of the grade type while the column header consists of the status type. (see database)

Questions:

I'm doing up a daily report to reflect the attendance of the students of the various grades

AND it is not always the case that I'll have students of all the grades type (indicated in the grade tbl) and rarely, i've students on student exchange or admitted to hospital.

However, I want to reflect all the grade types and status type in the cross tab query / report and for the return value to be either NULL or blank.

A. How is it possible?

B. How to update a cross tab query as attendance taking is a daily thing?

 

[Sammy B]

I believe you will need to structure your tables differently. You would create an Attendance table with StudentID, Date, StatusTypeID. The same would apply for Grades. Grade Table would have StudentID, Year and Grade.

Sam