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.