Calculated Status - Does it need to be stored in a table field?

[JMongi]

So, I have a table "MaintTaskLog" that contains the various dates associated with a maintenance task being worked on (Task Scheduled, Task Started, Task Completed, Task Verified). These various dates and combinations of their existence or lack of existence can be used to determine the task status at any time. Since this is the case, it seems that I do not need to store the task status in my table. However, there may be times when I would like to display this status in a continuous form. Do I need to store the status value in a field in this case? Or can I still determine it using VBA on an as needed basis?

 

[Gasman]

I would write a function to calculate the status and use that everywhere?

 

[JMongi]

I'm not entirely sure how that would work in the scenario I proposed (used in a continuous form listing all tasks and their current status)?

 

[Isaac]

I'm not entirely sure how that would work in the scenario I proposed

Your function would generate an output column in the query that underlies the continuous form.

 

[JMongi]

Ok. So a query based on my table with an output column from the function. Ok. Cool. Thanks. One less list table to worry about. Thanks!
P.S. Don't worry, I'll be back to figure out how to do that when the time comes...LOL!

 

[Galaxiom]

I would write a function to calculate the status and use that everywhere?

Although functions are a very tidy way to handle some situations one should be careful to not let them become a panacea. Native SQL statements are considerably more efficient.

 

[JMongi]

@Pat Hartman - I follow your logic if not the details. I think I've done that intuitively in some situations and not in other situations. I call it database mode. My brain thinks in normalization better at some times than others. I mentally followed your example with the status. How does that interact with a hypothetical date field? A status change date field that updates? Then a status log table like you mentioned to track previous changes?

 

[JMongi]

@Galaxiom - That's good to keep in mind. That was my general thought. If I can do something with the db structure that makes sense I'd rather do that than cover it over with VBA. Exceptions always exist, but, it seems like a good rule of thumb.

@Pat Hartman - To expand on my previous reply...

Let me know if this is what you had in mind. I create a separate table:

TskStat
TaskStatusID
TaskStatus

This table is related to a status field in my "MaintTaskLog" table from my first post. In my usage, a user would update this status field using a dropdown list in the task form. Then after updating in the form, a record would be created in the status log table which would contain the date of the status change as well. Do I have the general idea? (Edited for clarity)

 

[Minty]

You can do this both ways, I have used a calculated status based on other fields criteria, (in SQL Server, where it was part of a view, so the overhead in Access was minimal), the beauty of this is that it is always accurate, as it reflects the changes to the underlying record automatically. This works well in a very defined process. And if the status can be managed in the BE by relationships/records in a table you can easily add different status types.

I have also had a status table as a child set of records for a repair process, so when someone updated a repair a record was appended to indicate the status and who and when it was updated. It also allowed someone to manually add a new status record depending on other criteria.

Both have their merits, although the first method can get messy as @Galaxiom mentioned if it requires a function in a query to get the results.