[AccessD] query/procedure help

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



More information about the AccessD mailing list