If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Expiry dates query
Dear all,
I have a list of employees along with a list of expiry days example ID Passport expiry date Driving License expiry Date Insurance Expiry Date 5001 11/7/2009 11/9/2010 11/7/2009 5002 1/12/2010 11/9/2010 11/7/2009 I have used the following :Format(Date(),"yyyymm") and =(DateAdd("h",-24,Now())) i can only put it in one in of the expiry dates, what i need is an expression that gives me todays expiry dates by ID |
#2
|
|||
|
|||
Expiry dates query
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())) -- Ken Snell http://www.accessmvp.com/KDSnell/ "Tia" wrote in message ... Dear all, I have a list of employees along with a list of expiry days example ID Passport expiry date Driving License expiry Date Insurance Expiry Date 5001 11/7/2009 11/9/2010 11/7/2009 5002 1/12/2010 11/9/2010 11/7/2009 I have used the following :Format(Date(),"yyyymm") and =(DateAdd("h",-24,Now())) i can only put it in one in of the expiry dates, what i need is an expression that gives me todays expiry dates by ID |
Thread Tools | |
Display Modes | |
|
|