I have a database with two tables: docTbl and docStatTbl. docTbl has the primary key or the relationship, and docStatTbl has the foreign key. This is a one-to-many relationship.
docTbl consists of all of my documents by name (fields include 'docID_PK' (primary key field), 'docTitle' and 'creation_date'). docStatTbl stores the 'docStatus' and 'status_date' of my documents. One document can have multiple statuses over a period of time. For example:
document1 was "Inducted" on 1/1/2014
document1 was "Reviewed" on 2/1/2014
document1 was "Enacted" on 3/1/2014
I am attempting to write a query that returns all documents Where the 'creation_date' is <= 2/28/2014 AND where 'docStatus' was not 'Enacted' on or before 2/28/2014.
Here's what I have tried:
SELECT docTbl.docTitle, docTbl.creationDate, docStatTbl.docStatus, docStatTbl.status_date
INNER JOIN docStatTbl
WHERE ((docTbl.creation_date) <= "2014-02-28") AND
(((docStatTbl.docStatus)<>"Enacted") AND ((docStatTbl.status_date)<= "2014-02-28"));
Right now this returns any record with a creation date of <= 2014-02-28. But those need to be excluded from the results if the document ever had a status of "Enacted" on or before 2014-02-28. I know it's an issue with the logic, I'm just not sure how to fix it at this point.
Any help is greatly appreciated.