[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

MSAccess Query Question

Posted on 2014-02-27
4
Medium Priority
?
322 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
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.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

649 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