Date based SQL table manipulation (eg effective-date of PF)#

* validation for existing record vs update

SELECT * FROM Award, Company
WHERE (((Award.EffectiveDate) = (SELECT MAX(A1.EffectiveDate) FROM Award A1 
WHERE ((A1.AwardKey = Award.AwardKey) 
AND (A1.EffectiveDate <= Company.PayPeriodEnding)) 
AND Award.AwardSeq = (SELECT MAX(A2.AwardSeq) FROM Award A2 
WHERE (A2.AwardKey = Award.AwardKey) 
AND (A2.EffectiveDate = Award.EffectiveDate)))));