Solved Calculated Last Action Date

[MDKay]

I already have an alternative way of achieving this, however it is long winded and possible over complicated.

What i am looking to achieve is having a calculated date field that will show my Last Action date.

What i have:
Action One - Y/N
Action One - Date
Action Two - Y/N
Action Two- Date
Action Three - Y/N
Action Three - Date

Is it possible to create a calculated field to compare all three dates and tell me the most recent date?

My alternative is to have this update on the form so that if any of the actions are ticked off, the current date is updated into the Last Action field.

Any help or feedback would be appreciated.

 

[Minty]

I think you would need to build this into a function or look at the Switch() function that can be used in a query.

Switch(Action3=Yes ,Action3Date , Action2=Yes ,Action2Date , Action1=Yes ,Action1Date)

By doing it in the reverse order you would get the date from the lates action set to yes.

 

[theDBguy]

Hi. If you are not familiar with the term "normalization," I would suggest you look into it. What you have is a "repeating group," and it is against normalization rules.

 

[plog]

I already have an alternative way of achieving this, however it is long winded and possible over complicated.

The correct, most efficient way to achieve this is by properly normalizing your data. When you feel the need to add numerated fields (e.g. Action1, Action2, Action3, etc.) it's time for a new table for that data.

That means you need a new table for your Action data (let's call it tblActions). Instead of 3 actions taking up 3 fields in the existing table, it means 3 actions will take up 3 records in tblActions. When you do it that way it becomes trivial to find the latest action of each record in the existing table:

SELECT [ForeignKeyField], MAX(ActionDate) AS LastAction FROM tblActions GROUP BY [ForeignKeyField]

That's the correct way to handle this task.

 

[MDKay]

Many thanks for the support, however i may have confused things with the names i have used, these are not the actual field names.

Below is what i am actually using:

Quote Raised - [Y/N Checkbox]
Date Quote Raised - [Date]
Followed Up - [Y/N Checkbox]
Date Followed Up - {Date]
Survey Completed - [Y/N Checkbox]
Date Survey Completed - {Date]
Contract Signed - [Y/N Checkbox]
Date Contract Signed - {Date]

This is to effectively build a history of the order and when each stage is completed, the Last Activity Field i am creating will be used to filter orders that haven't had any action completed within a timeframe.

 

[theDBguy]

Many thanks for the support, however i may have confused things with the names i have used, these are not the actual field names.

Below is what i am actually using:

Quote Raised - [Y/N Checkbox]
Date Quote Raised - [Date]
Followed Up - [Y/N Checkbox]
Date Followed Up - {Date]
Survey Completed - [Y/N Checkbox]
Date Survey Completed - {Date]
Contract Signed - [Y/N Checkbox]
Date Contract Signed - {Date]

This is to effectively build a history of the order and when each stage is completed, the Last Activity Field i am creating will be used to filter orders that haven't had any action completed within a timeframe.

Hi. I could be wrong, but the names don't matter. I still think you have a repeating group. What happens if the business rules later change that requires you to keep track of another attribute? You will have to change your table structure and your forms' designs. Instead, you should have another table to store those information. Also, a Yes/No field is not really needed. If something was completed, then having a date of completion should be enough to tell you it was done.

 

[Deleted member 147267]

Not sure I see repeating data, but it's hard to follow. IMO it all depends on what the entity is. If it's a quote, I see a mix of quote and contract data, which is another issue. At the very least the checkbox fields are superfluous. If there is a date, the attribute is yes/positive/true. If there is no date, it is the opposite. To have both is not only unnecessary, it allows a condition where one can be true yet there is no date.

EDIT - what I wrote about quote and contract data mixing goes for surveys as well, so it appears there is a normalization issue that shows a need for at least 2 more tables on top of unnecessary fields in any/each of them.

 

[theDBguy]

Not sure I see repeating data...

Just to clarify that quote. I am not referring to "repeating data;" but rather, I am talking about "repeating groups." They are not the same thing.

 

[Deleted member 147267]

Sorry, I mis-typed. I should have said groups also, just to be consistent, but I had in mind that which came after post 4. In other words, if something can have 4 applicable dates (e.g. created, approved, completed, closed) that isn't repeated groups IMHO. It is 4 dates that are 4 attributes of the same thing. That thing could be a work order for example. If at some point you need another date field, it is either because you didn't plan and design well, or the process has changed. You can't do much about the latter.

@MDKay, there is a take-away here I think, about posting pseudo data/design elements when it's not necessary. It seldom assists with getting focused responses. On the contrary, it often causes confusion and speculation.

 

[theDBguy]

Sorry, I mis-typed. I should have said groups also, just to be consistent, but I had in mind that which came after post 4. In other words, if something can have 4 applicable dates (e.g. created, approved, completed, closed) that isn't repeated groups IMHO. It is 4 dates that are 4 attributes of the same thing. That thing could be a work order for example. If at some point you need another date field, it is either because you didn't plan and design well,

@MDKay, there is a take-away here I think, about posting pseudo data/design elements when it's not necessary. It seldom assists with getting focused responses. On the contrary, it often causes confusion and speculation.

Hi. To each his own, I guess. But the definition of a "repeating group" is storing the same attribute in one record. For example, cell phone, home phone, fax number, and pager are all considered as a repeating group although they have different values because they represent different means of communication for the same record. So, created, approved, completed, and closed are also repeating groups because they represent "actions taken" on the same record. You wouldn't store fields like first child, second child, third child like that, at least I hope not. The clue is when you have to change the db design just to add one or more of the same type of information.

... or the process has changed. You can't do much about the latter.

The way you would handle to avoid that is to use a separate table for the attribute. For example, if you had a table as follows:

tblStatus
1, Created
2, Approved
3, Completed
4, Closed

You can then have, in your data table, something like this

Project1, Created, 2020-01-01
Project1, Approved, 2020-02-01
Project1, Completed, 2020-03-01

And if later, the business rules changed that management wants to add a status of Reviewed, then all you have to do is add a new record in tblStatus and then add a new record to the data table with the date for the new action. No need to change table or form designs.

Hope that helps...

 

[Deleted member 147267]

But the definition of a "repeating group" is storing the same attribute in one record.

We agree to disagree. A create date and a closed date are often considered to be two distinct attributes whereas you seem to be lumping them together just because they are dates, which seems you're influenced by data type rather than data. I suppose your way is really an example of normalizing until it hurts, whereas the other way is more like de-normalizing until it works. I would suggest I'm not alone in thinking this way because here is an example of dates, addresses, phone numbers, etc (each as they apply to the entity in question) by someone whom I'd say you're familiar with and whose db knowledge is right up there. Notice the tables with at least two fields of the same data type, be that date, address, phone numbers, whatever. It's just one example of many that I'm sure I can find, but will just respectfully say neither one of us is "wrong".
https://www.access-diva.com/dm12.html

 

[theDBguy]

I suppose your way is really an example of normalizing until it hurts, whereas the other way is more like de-normalizing until it works.

I would agree with that. Although we refer to them as normalization "rules," it doesn't mean everyone follow them. When it comes to the example of phones as a repeating group, I have also used them in one table. It's easy enough to ask the data what is this person's cell number, or their work number, etc. However, when the question then becomes, which date is the latest, that's probably when I would draw the line. Cheers!

 

[Deleted member 147267]

However, when the question then becomes, which date is the latest, that's probably when I would draw the line.

Can't recall a time when I ever needed to worry about which date was later when the dates were different attributes. If you want to know what the last create date is, then of course you would not put them in separate fields, giving you a problem as you've described. To use a work order table example, a table with a create date, approved date, submit date, active date, fini date and closed date would never fit the situation you described because each is a progression of a status. It would be impossible for a closed date to be earlier than a create date (barring any data screw-ups) so it is intuitive. Surely you would not have a separate table for this, repeating the work order PK number for each date when those dates are not the same attribute? Well, if you would, that's fine with me.

 

[theDBguy]

Can't recall a time when I ever needed to worry about which date was later when the dates were different attributes. If you want to know what the last create date is, then of course you would not put them in separate fields, giving you a problem as you've described. To use a work order table example, a table with a create date, approved date, submit date, active date, fini date and closed date would never fit the situation you described because each is a progression of a status. It would be impossible for a closed date to be earlier than a create date (barring any data screw-ups) so it is intuitive. Surely you would not have a separate table for this, repeating the work order PK number for each date when those dates are not the same attribute? Well, if you would, that's fine with me.

Looks like we've gotten away from the topic. It wasn't me who needed to know the "latest" date. It was the OP - the topic of this thread was to find out "the last 'action' date."

 

[Deleted member 147267]

Maybe so and to the OP I apologize for any unwanted distraction although something might have been learned from it. My comments were only driven by yours. I will unwatch this one and stay out of it now.

 

[CJ_London]

based on the example data provided in post #5 and assuming these are all fields in one record (the Micron way ) I agree you don't need the checkboxes. you could use a nested nz function

nz(Date Contract Signed,nz(Date Survey Completed,nz(Date Followed Up,Date Quote Raised)))

But this presumes that events have to follow a specific order. And if further events are deemed necessary or optional then a redesign of the table and related queries, forms and reports will be required.

So in that sense I'm with DBG - this sort of data is based on business process - and processes change over time.

 

[The_Doc_Man]

As I have tried to say in other threads that overlap this topic, ... when the database and the business process disagree, the database is usually wrong. So whether those items should be in a subsidiary table or as separate attributes in the same table will depend on the business itself.

The only referents I have for this were the Navy Enterprise Data Center "Engineering Change Request" and "Customer Service Request" - both of which had multiple stages. But there, the design was based on typical Navy overkill.

The ECR had a status code (Entered, Approved, Rejected, Assigned, In Work, In Testing, Verified, Abandoned, Closed) and that status code could only be one of those choices. Further, they had to always be in a particular order. We had a date for "last status change." After that we had a separate "Action History" table that listed the transitions and dates - but the main ECR form only showed the current state and date.

My take is that if in MDKay's table those four events were actually independent events, they belong in the main record, but if they are sequentially constrained events, they belong in a child table and only one status and date should be in the main table.

 

[MDKay]

@The_Doc_Man that is correct, there is no standard order to the process. As an example, while the building is in progress at a customers house, a neighbour may ask to have the installer to take a look at theirs, the installer will then perform the survey and provide us with the details to generate a quote, changing the above order.

Whilst the example tick boxes are superfluous, there are others that are needed for other side actions that would not move one the "Status" of the Quote, for example once the contract is signed we are Awaiting Installation, but during this time, a date will be set for installation, files to run the saw will be created and uploaded to USB to have the cuts made ect. None of these actions need dating but we need to know if they are done.

 

[theDBguy]

None of these actions need dating but we need to know if they are done.

Hi. I would suggest using a date anyway. With it, at least, you gain additional information/advantage - knowing "when" it was done. Just a thought...

 

[The_Doc_Man]

@MDKay - your own description just added to a reason to not do it the way you are doing it.

You have multiple events being run simultaneously yet somehow from the description you just gave us, there is some linkage because you have what appears to be "components" of the greater job, each with these events that you describe. You face serious proliferation of event tracking, which is the other reason why one might look to normalization.

You can probably get away with what you are doing now but as noted, if someone ever comes up with a fifth event, your DB will be nearly useless until you can retrofit. And having subsidiary related events contributing to an overall status is a guarantee for headaches from all the rollups.