Search Columns with Multiple Date Fields to find a due date

[scopes456]

Hello All, i have a table that use to track employee training. The table has multiple columns with Date information on it. ( see example below)I would like a query to find the columns that has a due date 12 months form the date in the column. ( for example under CPR for MIKE , he did it on 1/1/2016 , i would like the query to find it when it comes due 12 months 1/1/2017). The query will return His name to indicate that his training is due


Table ( Employee_track)

NAME | CPR TRAINING | FIT TEST | EQUPI CHECK

JOHN | 2/1/17 | 3/1/17 | 2/1/17
MIKE | 1/1/16 | 2/1/17 | 2/1/17

 

[jdraw]

Have you considered a normalized design with these tables (maybe more depending on your needs)

Employee-->EmployeeTookTraining<----Training
John          2/1/17                          CPR
John           3/1/17                         FIT

 

[sneuberg]

If you would be happy to call 365 days twelve months then something like

SELECT Employee_track.FirstName, Employee_track.[CPR TRAINING], Employee_track.[FIT TEST], Employee_track.[EQUPI CHECK]
FROM Employee_track
WHERE (((Employee_track.[CPR TRAINING])<Date()-365)) OR (((Employee_track.[FIT TEST])<Date()-365)) OR (((Employee_track.[EQUPI CHECK])<Date()-365));

If you are not to far into the design of your system I suggest considering a different table structure, like Jdraw suggests.

 

[Galaxiom]

If you are not to far into the design of your system I suggest considering a different table structure, like Jdraw suggests.

In this case, "not too far in" means that the database is not about to be deprecated.

A normalized design will allow other kinds of training to be added later without having to modify the design.

Also you should store the renewal period in a table rather than hard coding it into queries. With the right design, the same query will be able to easily report on the date that every employee needs to renew each type of training.

 

[scopes456]

all thank you for your help, i will look at a redesign but you all set me on the right direction, i was able to use sneuberg code.