Your problem is that the table design is flawed by having separate columns
for each class. This is what's known as 'encoding data as column headings'.
A fundamental principle of the database relational model, 'the information
principle' is that data is stored as explicit values at column positions in
rows in tables, and in no other way.
A correct design would be to have a separate table related to an Employees
table on EmployeeID with columns EmployeeID, ClassID and AttendanceDate.
ClassID would be a foreign column referencing the key of a Classes table.
This ClassAttendances table in fact models the many-to-many relationship
between Employees and Classes by resolving it into two on-to-many
relationships (sometimes informally called a 'junction' table or similar).
You then simply need to join the tables and apply the criteria to the
AttendanceDate column.
However, you can torture the flawed table into confessing by means of a
UNION ALL
operation:
PARAMETERS
[Type the beginning date:] DATETIME,
[Type the ending date:] DATETIME;
SELECT [EmployeeID], "Class A" AS Class,
[Class A]
FROM [YourTable]
WHERE [Class A] BETWEEN
[Type the beginning date:] AND
[Type the ending date:]
UNION ALL
SELECT [EmployeeID], "Class B" AS Class,
[Class B]
FROM [YourTable]
WHERE [Class B] BETWEEN
[Type the beginning date:] AND
[Type the ending date:]
UNION ALL
SELECT [EmployeeID], "Class C" AS Class,
[Class C]
FROM [YourTable]
WHERE [Class C BETWEEN
[Type the beginning date:] AND
[Type the ending date:]
UNION ALL
SELECT [EmployeeID], "Class D" AS Class,
[Class A]
FROM [YourTable]
WHERE [Class D] BETWEEN
[Type the beginning date:] AND
[Type the ending date:];
But you really should consider normalizing the table, which is very easy to
do, as a UNION ALL operation like the above, but without the parameters, and
a WHERE clause of 'WHERE [Class A] IS NOT NULL', 'WHERE [Class B] IS NOT
NULL' etc in each part as appropriate, will return all rows and can be used
as the basis for an append query to fill a correctly structured table.
BTW note that date/time parameters should be declared as such, as above.
Otherwise there is a risk of a parameter value entered in short date format
being misinterpreted as an arithmetic expression rather than a date and
giving the wrong results.
Ken Sheridan
Stafford, England
Ace said:
I am creating a report that that request Between [Type the beginning date:]
And [Type the ending date:] in 4 seperate fields (classes employees have
taken). I only want to retrieve the records that contain the inputted dates
(for a weekly roster), except I am getting older records in some of the
fields. Example: Ask for classes taken between 1-10-09 asd 1-20-09. Result:
Employee A; Class A: 1-12-09; Class B: 1-19-09; Class C: 7-9-08 (don't want
this record to show); Class D: 1-12-09.
Employee B; Class A: 1-12-09; Class B: (value is null, this is okay); Class
C: 1-17-09; Class D: 1-12-09
How can I set the criteria to only show the date ranges inputted without
having to create a seperate class roster report? Thank you in advance for any
help.
