One reason you're having trouble trying to set this up is because your table
structure is not normalized -- meaning you have a separate field for each
type of date. What you need is a record for each type of date.
tblDocumentType
DocumentTypeID
DocumentTypeDescription
tblEmployeeDocumentType
ID
DocumentTypeID
ExpiryDate
You put the different types of documents into tblDocumentType (e.g.,
Passport, Driving License, Insurance). Then you put a record in
tblEmployeeDocumentType for each unique combination of employee/document
type, and the ExpiryDate field holds the value for that type of document for
that employee.
Then your query is very simple to do. You put the criterion on the
ExpiryDate field, and the query will return all types where the criterion is
met for an employee.
SELECT * FROM tblEmployeeDocumentType
WHERE ExpiryDate >=(DateAdd("h",-24,Now()))
Using your current setup, you'd have to put the >=(DateAdd("h",-24,Now()))
expression on all three expiry date fields in your table, using OR logic,
but you'd have to return all the date fields in the query.
Another way of using your current setup is to use a UNION query, where each
subquery in the UNION query returns the value of one field (note: you cannot
build this type of query in the Grid View, it must be built in SQL View):
SELECT ID, [Passport expiry date] AS ExpiryDate, "Passport" AS DocumentType
FROM YourTableName
WHERE [Passport expiry date] >=(DateAdd("h",-24,Now()))
UNION ALL
SELECT ID, [Driving License expiry Date] AS ExpiryDate, "Passport" AS
DocumentType
FROM YourTableName
WHERE [Driving License expiry Date] >=(DateAdd("h",-24,Now()))
UNION ALL
SELECT ID, [Insurance Expiry Date] AS ExpiryDate, "Passport" AS DocumentType
FROM YourTableName
WHERE [Insurance Expiry Date] >=(DateAdd("h",-24,Now()))
