Query to designate project At Risk/On Track based on activities being past due/on time

[ucantremember]

My apologies in advance: I know just enough about Access to be dangerous. I have a table for projects & linked table for the activities related to each project. The goal is to create a query and/or report that will add a column to designate a project as "On Track" if the activities are on time or "At Risk" if any activities are past due. I have a formula in a query [/Condition: IIf([DateDone] Is Null And [DueDate]<Now(),"At Risk","On Track")] that designates the project as on track or past due, but I cannot get it to display correctly. It will successfully show me everything that is at risk OR everything that is on track when filtered for either option, but if I try to display both it displays duplicates, as in project number both on track and past due as many times as there are past due activities, and other fields go haywire displaying nonsense, Chinese symbols, Farsi, etc. . I am grateful for any guidance or direction.

[Minty]

Can you post up the exact full sql of the query from the query designer?
And possibly a picture of the query in the design window so we can see the tables.

It sounds like a design issue or possibly that you need a sub query to give you the latest activity?

[ranman256]

use:
IIf(isnull([DateDone]) And [DueDate]<date(),"On Track","At Risk")

[field] is null ...is for query criteria

[Micron]

If you want a flag based on activity status, why not just use conditional formatting in the report or form? Set the textbox background to yellow if due within 2 days and red if it's past due - or something like that. No code, no query calculations required. If you're only interested in the project status it will be more complicated because you'll have to get the Max of activity due date and decide how to flag the project status based on that. IMO, the former is not only far easier, it makes it obvious as to which activities are responsible for any warning you might raise.

[orange]

You will get focused responses if you provide sample data with a clear description of your environment and your expected/desired result(s)?
At Risk could be unlikely, possible, definite with relative associated consequences.
It seems At Risk,Yes or No may not be reasonable???

[ucantremember]

Can you post up the exact full sql of the query from the query designer?
And possibly a picture of the query in the design window so we can see the tables.

It sounds like a design issue or possibly that you need a sub query to give you the latest activity?

Thanks for the quick response! It is highly likely to be a design issue, as I am stumbling through this out of sheer necessity.

FYI, each project is a deviation, hence the "dev" references. Here is the SQL:
SELECT DISTINCT Dev_Activities_Table.[Dev#], IIf([DateDone] Is Null And [DueDate]<Now(),"At Risk","On Track") AS Condition
FROM Dev_Activities_Table
WHERE (((IIf([DateDone] Is Null And [DueDate]<Now(),"At Risk","On Track"))="At Risk"));

[ucantremember]

Thanks for the quick response! I tried your formula, but it works like mine. I can show either past due or on track, but displaying both displays duplicates.

[ucantremember]

If you want a flag based on activity status, why not just use conditional formatting in the report or form? Set the textbox background to yellow if due within 2 days and red if it's past due - or something like that. No code, no query calculations required. If you're only interested in the project status it will be more complicated because you'll have to get the Max of activity due date and decide how to flag the project status based on that. IMO, the former is not only far easier, it makes it obvious as to which activities are responsible for any warning you might raise.

Thanks for the quick response! The conditional formatting is built into the forms & reports used by folks everyday involved in each project, but management only wants to see the numbers: How many total, how many on track/past due, and so on. They don't want to scroll through each project to see that status, hence the report I am trying to create with those details at a glance. Right now I gather total number from the query for the project list table & the number past due from the activities table, manually, but I would prefer something to do it automatically.

[Micron]

Best would be to copy, compact and zip the db. Remove or randomize sensitive data; include only what is needed to replicate the issue and post that here. Going back to your first post, it should be doable but it helps to be able to see the sql and data structure.

[Minty]

What happens if you remove the Distinct keyword and all of the where clause?

SELECT Dev_Activities_Table.[Dev#], IIf([DateDone] Is Null And [DueDate]<Date(),"At Risk","On Track") AS Condition
FROM Dev_Activities_Table