Solved

MSAccess Query Question

Posted on 2014-02-27
4
311 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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

840 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