Stauts Challenge (V2002)

Topic

New Reply

I am tracking due date progress and need to calculate the current status. Here are the givens:
There are 18 Milestone Due Dates
There are 18 Milestone codes (Actual, N/A or Forecast)
There are 18 Milestone Completion Dates
There are 3 overall Statuses (Red, Yellow and Green)

– To be coded as Green, due dates prior to today must be coded as Actual or N/A.
– If all prior due dates are coded as such, then the due date prior to today must have been completed on or before the due date.
– If the prior due date was missed, the database should look at the next due date to see if it was completed early then code the status as Green if it was completed early (this enables the user to switch from Red to Green by completing a future due date).
– If these conditions are not met, the part is coded as Red (one or more prior codes Actual or N/A or the most recent Milestone was completed late and the next due date was not completed early).
– The last code impacts a Green status…if a status is Green and the next due date is within 10 days and is not complete, the status should switch to Yellow.
– This enables the user to get a warning before it changes to Red.

I had an old database dealing with only Red and Green statuses and used a series of IIF( statements to complete the logic. The addition of Yellow status leads me to believe a function would work better. If this is confusing, the attached database might help (open the form – Part_ProgramTiming_sf)

At the very least, it is a challenge.
Thanks!
Andrew

Reply | Quote

Replies

Your table design violates relational database design, making it very hard to work with. I would split Part_ProgramTiming into two tables:

The first table would contain PartPPTID (primary key), Date1 and Date2.
The second table would contain PartPPTID (foreign key), MilestoneNo (1 through 18), DueDate, Code and CompletionDate.

The second table would contain a separate record for each part – milestone combination.

It’s possible to write VBA code that transfers the data from Part_ProgramTiming into the new tables.

Is there any chance that you can adopt this data design, or is that out of the question?

Reply | Quote

We picked this design up from another set-up and changing it would require modifying a lot of other areas (of which we don’t control). I am trying to convince them to relinquish the other areas and allow us to change everything at once but now our hands are tied. Therein lies the complexity and the challenge.
Thanks,
Dashiell

Reply | Quote

See attached version. I created a module with a public function, and used it in the control source of two text boxes on the form.

Note: the text boxes update automatically if you change Date1 or Date2, but not if one of the 18 due dates, completion dates or statuses is changed, because the function looks at the table, not at the form. You should see the text boxes change if you move to another record and back.

The function is probably more complicated than necessary, but I didn’t want to spend too much time on it.

Reply | Quote

Hello Hans,
I can’t thank you enough…that fantastic string of code replaces my lengthy series of IIF( statements. I tested 100 scenarios and everything tied out perfectly except one. I worked long and hard trying not to bother you but to no avail. The attached form contains one record that is calc coded Green when it should be coded Red. Please don’t spend much time on this but any help would be greatly appreciated!!!
Andrew

Reply | Quote

You originally stated[indent]

---

If the prior due date was missed, the database should look at the next due date to see if it was completed early then code the status as Green if it was completed early

---

[/indent]Although with hindsight, it makes sense to require that the status for the next due date is either Actual or N/A, you didn’t state it explicitly, so I didn’t program it. The attached version of the code checks this extra requirement.

Reply | Quote

Thanks Hans…I’m sorry I wasn’t more clear. Again, I truly appreciate it, I can’t tell you how much easier the code will make things for me and I appreciate all your help!!

Reply | Quote