subreport issue (1 Viewer)

dakcg

Registered User.
Local time
Today, 12:05
Joined
Aug 24, 2001
Messages
88
Greetings,

I have been working on a database for a school system. This database tracks the teacher absences and the reason for the absences; ex: Sick day, Family Illness, Jury duty. I am trying not to hard code the type of absences into the subreport, but allow it to change as reasons are changed or added into the database.

I have a table that stores the absence dates and the reason for the absence. The problem I am having is this: Say there are 10 different reasons for absences and the teacher only has used 5 of them, how do I get the subreport to show '0' for all the absence reasons that didn't show up in the table that stores the absences. I tried using temp tables with update queries, but that was a dismal failure.

I have one week to figure this out, while the school is out on their week break. Any help will be greatly appreciated.

Blessed be

Doreen
.
 

WayneRyan

AWF VIP
Local time
Today, 12:05
Joined
Nov 19, 2002
Messages
7,122
Doreen,

Good that you have a seperate table to record the absences. Do you have
another table that stores the absence types. If you do, then the following
is quite easy:

Code:
Select TeacherID, AbsenceType
From   tblAbsences
Where  AbsenceType Not In 
   (Select AbsenceType
    From   tblAbsenceTypes)

Although Access (JET) doesn't handle nested queries too well, it should not
be a show-stopper. Logically, it is quite easy.

Wayne
 

Users who are viewing this thread

Top Bottom