Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 214
  • Last Modified:

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
0
ferguson_jerald
Asked:
ferguson_jerald
  • 2
2 Solutions
 
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
 
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
 
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now