I am a mech engineer, and started using access recently.
I have to run monthly reports on Part Numbers. Everytime the new data is sorted using certain criteria, the ranking of these part numbers change. I need to track the current rank and the previous rank (prior to updating) in MS Access. I have created a query to integrate data from couple of tables into one table. I need to see the current rank and the previous rank in this generate table. How do I do this?
Your help is appreciated.
Thanks for the reply.
When I create the table for the first time, how do I create the ranking? I run a query to integrate the different tables into one. I need to create a new column in this table and start the ranking from 1. Is there a way to create this when I run the query?
There are 4 tables from which I integrate data using a query, which then is stored in a "Result" table using a certain fields for sorting data. I need to rank each row in the "Result" table with a unique values, always starting from one.
Before I run an update, I assume that I copy this "Result" table into an "Archive" table. After the updation there might be changes in the Ranking Order, and I would copy the Ranking from the "Archive" table to get a comparison on the Ranking Status.
I have shown below some of the columns from the "Result" table