MS Access 2010 query logic assistance needed

Hello Experts:

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
FROM docTbl 
INNER JOIN docStatTbl
ON docTbl.docID_PK=docStatTbl.docID_FK
WHERE ((docTbl.creation_date) <= "2014-02-28") AND 
(((docStatTbl.docStatus)<>"Enacted") AND ((docStatTbl.status_date)<= "2014-02-28"));

Open in new window


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.

Thanks,
J
ferguson_jeraldAsked:
Who is Participating?
 
Russell FoxDatabase DeveloperCommented:
You can also move the "Enacted" parameter into the join:
SELECT docTbl.docTitle, docTbl.creationDate, docStatTbl.docStatus, docStatTbl.status_date
FROM docTbl 
	LEFT JOIN docStatTbl
		ON docTbl.docID_PK=docStatTbl.docID_FK
		AND docStatTbl.docStatus = "Enacted"
WHERE docTbl.creation_date <= "2014-02-28"
	AND 
		(
		docStatTbl.status_date IS NULL
		OR 
		docStatTbl.status_date > "2014-02-28"
		)

Open in new window

0
 
Gustav BrockCIOCommented:
Try with:
SELECT docTbl.docTitle, docTbl.creationDate, docStatTbl.docStatus, docStatTbl.status_date
FROM docTbl 
INNER JOIN docStatTbl
ON docTbl.docID_PK=docStatTbl.docID_FK
WHERE ((docTbl.creation_date) <= #2014-02-28#) OR
(((docStatTbl.docStatus)<>"Enacted") AND ((docStatTbl.status_date)<= #2014-02-28#));

Open in new window

/gustav
0
 
ferguson_jeraldAuthor Commented:
Thanks for the replies.  This actually worked:

SELECT docTbl.docTitle, docTbl.creationDate, docStatTbl.docStatus, docStatTbl.status_date
FROM docTbl
LEFT JOIN docStatTbl
ON docTbl.docID_PK=docStatTbl.docID_FK
WHERE (docTbl.creation_date <= "2014-02-28") AND
((docStatTbl.docStatus<>"Enacted") AND (docStatTbl.status_date<= "2014-02-28"));
0
 
Gustav BrockCIOCommented:
Then your date fields are not date fields but text fields.
That is never a good idea.

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.