Solved

MSAccess Query Question

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

Containers & Docker to Create a Powerful Team

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

691 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