>First of all, to keep the most recent records in each group, it would be convenient to use a timestamp for each record. Another possibility would be to simply use the recno() to figure out the latest records; however, with a timestamp, it should be easier to create relevant queries; also, it would be scalable to a C/S environment.
>
Hi Hilmar,
Here are my thoughts:
1. Run GenDBC to make an empty copy of a database.
2. I need a select statement to select two most recently run jobs per each job type, I'm not sure, how to write this select. I do not remember, if the Jobs table has a timestamp, but I want to have recent jobs per each type. Some jobs could be run year ago (for some job type), but I still want them, since I want to have every possible Job Type.
3. Once I have JobIDs with these jobs, I can create selects for all related tables and then append from the resulting cursors...
Actually, I asked my colleague to create this "light" version of the database for me, but I'm thinking, how to simplify this task for her...
>An idea would be to use queries to select the data in the jobs table. Then, with the primary keys, you can fetch data from the related tables.
>
>Another possibility is to delete all data that is NOT to be included in the "light" version. This might be simplified through RI.
>
>>Hi everyone,
>>
>>We have a big database with ~ 20 related tables inside. There are no triggers or rules, as I know. One of the tables is called Jobs and has JobID (I), JobType, LastRunDate, etc. fields. This table has several dependent tables, such as JobSteps, for example. I want to have a light version of that database with two most recent jobs per each job type. How can I automate the process of creating such "light" version of a database? And I am also curious, if a generic tool, which allows to easily do such task, already exists.
>>
>>Thanks a lot in advance.
If it's not broken, fix it until it is.
My Blog