Find all null times of all PupilID IN THIS DAY

[MARELI]

hi!

i realy searched and tried to solve my problem and i have no success, so i m here again I hope someone here will help me.
here my problem:

i have a table with:

PupilId
DateCourse
LessonA
LessonB
LessonC
LessonD
LessonE
LessonF
LessonG

each lessonX is a begin lesson time, going from 07:00 to 17:00 and is 45 minutes long.


the continous-form which i build retrieve lessons values by the criteria: DateCourse=date() and they (the fields lessons) are not sorted .
IMPORTANT [\B]
each row in this form takes different pupil and his first Lesson can be LessonD or LessonF...
(assume that we are talking about private lessons for each pupil)
so you can see for pupil X:

LessonA = 13:30
LessonB = null
LessonC = 08:45

and for pupil Y you can see:

LessonD = null
LessonB = 07:00
LessonC = 08:45

and so on...

what i m looking for is to find all null times of all PupilID IN THIS DAY, in case one pupil can not take a lesson so i want to find another one who can talk a lesson in that specific time.

I hope i m clear enogh... with my poor english

I thanks any help or idea how to compish this

MARELI

 

[dhookom]

Your main issue is that you are "committing spreadsheet". A more flexible data structure would have fields like:

PupilID
DateCourse
LessonTime

Each lesson should be a new record. If you can't change your structure then you can build and search a Union Query
SELECT PupilId, DateCourse, "LessonA" as Lesson, [LessonA] as LessonTime
FROM tblA
UNION ALL
SELECT PupilId, DateCourse, "LessonB", [LessonB]
FROM tblA
UNION ALL
SELECT PupilId, DateCourse, "LessonC", [LessonC]
FROM tblA
...etc...

The union query can easily be searched by date and for either null or non-null values by date and lesson time.


LessonA
LessonB
LessonC
LessonD
LessonE
LessonF
LessonG

Duane
MS Access MVP
Find out how to get great answers faq219-2884.