Solved

MSAccess Query Question

Posted on 2014-02-27
4
312 Views
Last Modified: 2014-04-23
Hello,

I have the following table structure:

TaskJob: (definitions of tasks)
TaskJobID - int
TaskJobName - string
TaskJobDaysToAlert - int

Tasks: (entry for every day the the particular task is completed)
TaskID - int
TaskJobID - int
TaskDateComplete - date
TaskSuccess - bool

I'm trying to construct a query in MSAccess to show me the TaskJob's that have not had a successful run in the allowed number of days to alert.  So if a TaskJob hasn't had a successful task in the last three days, it's info should be returned by the query.   Also, if the job hasn't run at all in 3 days, then the query should also return that.

I've got the DateDiff ('d', TaskDateComplete, Date()) part concept working, but I'm not sure how to setup a join on the tables.  When I attempt to use a GROUP BY on one of the subsets for MAX (TaskDataComplete), I also need it to only give me the MAX where TaskSuccess is true.  

Can anyone offer any pointers?

Thanks
0
Comment
Question by:compsol1993
  • 2
  • 2
4 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39892225
Try this:

SELECT * 
FROM TaskJob j LEFT JOIN Tasks t ON j.TaskJobID  = t.TaskJobID 
WHERE (TaskDateComplete BETWEEN (Date() - 3) AND Date()) OR (TaskDateComplete IS NULL)

Open in new window

0
 

Author Comment

by:compsol1993
ID: 39892231
Thanks, that does help, but I also need to consider how to incorporate TaskSuccess = True

I'll try adding it to the WHERE clause logic, does that seem correct?
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 39892241
Yes... I think you can also drop the = NULL check which I had included to test for jobs that aren't present in the tasks table:

SELECT * 
FROM TaskJob j LEFT JOIN Tasks t ON j.TaskJobID  = t.TaskJobID 
WHERE (TaskDateComplete BETWEEN (Date() - 3) AND Date()) AND j.TaskSuccess = TRUE

Open in new window

0
 

Author Closing Comment

by:compsol1993
ID: 40017715
Thanks
0

Featured Post

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question