Retreive only last date

[lochnez49]

I'm using MS Access to analyze SKUs in our ERP database to determine if they are still active. To do so I export the SKU list and I'm looking at the date that the status of the SKU was last changed in the inventory organization it resides in. We have multiple inventory orgs and enable SKUs in more than one. Sometimes the SKU does not get activated in an Org and remains pending. Sometimes it will be dropped in one org but not another.



The SKUs are created in a Master Org and then enabled in a child org. There is a Creation Date and an Effectivity Date for each. The creation date is static, but every time I change the status, the effectivity date changes. Multiple changes results in multiple effectivity dates for the same org.

I'm trying to run a query in MS Access that will list only the last date the SKU was changed in each org. This way I can see how long a SKU has been in a particular status. What I get is every record for every effectivity date. Using the criteria [Effectivity Date] > [Creation Date] doesn't provide all records because there will be times when the SKU was never changed. Those records will not show in the results, but I need to see them.

Can someone help me with a criteria formula or some other method of filtering for only the last effectivity date?

Query fields are:
SKU Descripton Inv_Org Creation_Date Status_Code Effectivity_Date

Any suggestions are greatly appreciated.

[kd2017]

Make an aggegate query. Group by SKU (I'm guessing) and Max(effectivity date).

I can't give you the exact query as I don't know what your tables look like.

SELECT SKU, Max(Effectivity_Date) AS LatestDate FROM [your table or query here] GROUP BY SKU;
Or something like that...

[June7]

Possibly need to also include Inv_Org field in the grouping.

[Micron]

list only the last date the SKU was changed in each org. This way I can see how long a SKU has been in a particular status

I'm not seeing how the Max of the effective date will tell you this. Would you not need to compare the Max of the effective date against the prior effective date, but if that is non-existent (was never changed) you'd need the create date? To get the prior effective date will require a sub query IMHO. If I understand the goal, this is more complicated than what's being proposed.

[kd2017]

Yeah, what both of those guys said. (they're much better at this than me... listen to them)

SELECT SKU, Inv_Org, Max(Effectivity_Date) AS LatestDate FROM [your table or query here] GROUP BY SKU, Inv_Org

Will get you the last Effectivity Date for each SKU and Inv_Org but it doesn't tell you how long since the previous effectivity date if that's what you're after.

[lochnez49]

Thanks for the suggestions. I'm actually on vacation this week but will try some of what I see here when I'm back in the office.

[lochnez49]

Thanks again for all the input. It provided the push I needed to figure it out. I was looking at the "last" of the effective date when I needed to be looking at the "max" effective date (thanks for that!). Also, I was looking at the status code, but I needed to look at the "last" of the status code. I don't need to see when it went from Pending to Active to Inactive, only when it went to Inactive, or whatever the last status is. And to see how long it's been in that status, not how long it was between changes.

Again, thanks!

[Micron]

Where I used to work, StatusUpdate was a field common to many tables, and it's purpose was to know when it was put into whatever the current status was. Easy enough to compare that date to a current date and know how long it has been in a particular status. However, there were a few metrics that would have been possible if there was a date per status - such as how long it took for a work order to be approved after it was submitted for approval. This isn't possible when the StatusDate gets updated at every change. I thought you wanted something more complicated than what you seem to have arrived at. Glad you got it solved anyway.