I have a table that contains part information. There is exactly one record for each part. I am going to be updating these records with information from the suppliers of these parts. I plan to import this data. I would also like to maintain a log of these changes. At present I use a single textual log field for each record. Values for data fields are captured on field entry and at field exit and when there is a difference, the old value and the new value are written to the log field as a change. This approach will not work with an import (as far as I know).
My desired approach is to simply import data as new records. While the part number will remain the same, I plan to create a unique field to differentiate the multiple records that will be generated for each part number. The unique field will be some combination of the part number and the date/time on which the record was created.
I would like to continue to use a list view for the layouts that show this data to a user. However I would like to filter records to show one record for each part number and then only the most recent. I am drawing a blank on how to create a relationship that will automatically filter the list of records in this manner. As an aside, I also plan to show past values of the most recent filtered value in a Card Style window so that the history of a record can be viewed. I will maintain the logging function mentioned above to keep track of manual changes to existing records.
Any thoughts on the filtering setup?