Calculated Status - Does it need to be stored in a table field? (1 Viewer)

[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!

 

[Pat Hartman]

Typically, you would use two fields. A status field and a date field. Sounds like you have a separate date field for each status type. This is a poor design choice and violates first normal form.

If your movement from one status to another is progressive, you need to have a sequence number in the status table to define the order"

10, Task Scheduled
20, Task Started
30, Task Completed
40, Task Verified

If you need the ability to go backwards, handle that programatically.
If you need to keep a status log, use a separate table called tblJobStatus (or whatever thing you are talking about)
JobStatusID (autonumber PK0
JobID (FK to job)
StatusID (FK to status)
StatusDT

Normalizing the schema gives you flexibility. What happens if next week the user wants a new status called "On Hold"? A properly defined schema can handle this without programming changes.

 

[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.

 

[Pat Hartman]

I posted a suggestion for the status table if you need to keep each historical change, I'll repeat it:

JobStatusID (autonumber PK)
JobID (FK to job table)
StatusID (FK to status table)
StatusDT

When doing something like this, it is sometimes easier to leave the current status in the Job table. To implement that method, when the user changes the status, you programmatically change the status date. Don't let the user change the status date unless using todays date as the date for the new status will not be appropriate. In the BeforeUpdate event of the form, copy the .OldValue property for the two fields to variables. Then in the Form's AfterUpdate event, insert the history record.

SaveStatus = Me.StatusID.OldValue
SaveDT = Me.StatusDT.OldValue

The logic is a little more complex than that because you wouldn't create a history record for a new record, only for a changed record.