Den Patrino
patrinod at gmail.com
Thu Dec 23 13:40:22 CST 2010
Fellow listers ... I am working on a system (Access2003) that I need help with. Production data is written automatically to a table every 1 second. The data collection system is turned on approx. 2 minutes before a batch production is started and not turned off until sometime after the batch production has ended. This results in many records that are not required as data before production starts is meaningless as well as data after the batch is completed. The table currently has > 700,000 records. Hopefully the examples below illustrate the problem. 1) I need help to create of procedure that loops through the table records and for every unique BatchID, deletes all records with a batch weight of 0, except the record with the latest time. Example data. Pertinent Table fields and data. BatchID (text), BatchTime (date/time) BatchWeight (long) MP123 22-Mar-2010 13:43:54 0 MP123 22-Mar-2010 13:43:55 0 MP123 22-Mar-2010 13:43:56 0 MP123 22-Mar-2010 13:43:57 0 MP123 22-Mar-2010 13:43:58 0 MP123 22-Mar-2010 13:43:59 0 MP123 22-Mar-2010 13:44:00 0 MP123 22-Mar-2010 13:44:01 0 MP123 22-Mar-2010 13:44:02 0 MP123 22-Mar-2010 13:44:03 0 MP123 22-Mar-2010 13:44:04 0 ------------------------------------------ MP123 22-Mar-2010 13:44:05 0 'delete records previous to this. MP123 22-Mar-2010 13:44:06 1 MP123 22-Mar-2010 13:44:07 2 At the end of the batch run, there will be multiple records with the same weight. (the max. weight of the batch) 2) I need to create of procedure that loops through the table records and for every unique BatchID, deletes all records with a where the batch weight = maximum weight for the batch, except the record with the earliest time. Example data. Pertinent Table fields and data. BatchID (text), BatchTime (date/time) BatchWeight (long) MP123 22-Mar-2010 14:37:23 11531 ------------------------------------- MP123 22-Mar-2010 14:37:24 11531 - delete from here down MP123 22-Mar-2010 14:37:25 11531 MP123 22-Mar-2010 14:37:26 11531 MP123 22-Mar-2010 14:37:27 11531 MP123 22-Mar-2010 14:37:28 11531 MP123 22-Mar-2010 14:37:29 11531 MP123 22-Mar-2010 14:37:30 11531 MP123 22-Mar-2010 14:37:31 11531 MP123 22-Mar-2010 14:37:32 11531 MP123 22-Mar-2010 14:37:33 11531 MP123 22-Mar-2010 14:37:34 11531 MP123 22-Mar-2010 14:37:35 11531 MP123 22-Mar-2010 14:37:36 11531 MP123 22-Mar-2010 14:37:37 11531 TIA. Patty