Solved

MSAccess Query Question

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
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.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now