Kevin Waddle
thewaddles at sbcglobal.net
Fri Dec 24 13:00:37 CST 2010
Don,
Look at the DMax and DMin functions
Delete Zero Weights:
DELETE Table1.* FROM Table1 WHERE (((Table1.BatchID)="MP123") AND
((Table1.BatchTime)<>DMax("[BatchTime]","Table1","[BatchWeight] = 0")) AND
((Table1.BatchWeight)=0));
Kevin Waddle
thewaddles at sbcglobal.net
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Den Patrino
Sent: Thursday, December 23, 2010 11:40 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] query/procedure help
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
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com