Kevin Waddle
thewaddles at sbcglobal.net
Fri Dec 24 15:53:20 CST 2010
Don,
This will loop through the records…modify the SQL as required to match your table
Sub LoopThroughQuery()
Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Dim strSQLDelete As String
Set db = CurrentDb
strSQL = "SELECT Distinct Table1.BatchID FROM Table1;"
Set rs = db.OpenRecordset(strSQL)
rs.MoveFirst
DoCmd.SetWarnings False
Do While Not rs.EOF
strSQLDelete = "DELETE Table1.* FROM Table1 WHERE (((Table1.BatchID)='" & rs.Fields("BatchID") & "') AND ((Table1.BatchTime)<>DMax('[BatchTime]','Table1','[BatchWeight] = 0')) AND ((Table1.BatchWeight)=0));"
DoCmd.RunSQL strSQLDelete
rs.MoveNext
Loop
DoCmd.SetWarnings True
Set rs = Nothing
Set db = Nothing
End Sub
Kevin Waddle
thewaddles at sbcglobal.net
From: Den Patrino [mailto:patrinod at gmail.com]
Sent: Friday, December 24, 2010 11:29 AM
To: Kevin Waddle
Subject: Re: [AccessD] query/procedure help
Kevin ...
Thanks for the quey. I've used the DMax and DMin functions in 2 separate queries and never thought of combining the 2.
My problem now is how do I cycle through the table without hard-coding the BatchID number and running the query.
Something like: (I'm not sure how to do this)
For each BatchID in rs
DoCmd.RunSql "theQuery"
End for
Thanks for your suggestion of combining the DMin and DMax. The problem of going through all BatchID's has been driving me nuts
With your query, if I can figure out the rest it will make things much easier.
It's really good to have people like you share their knowledge.
Thanks agains
Dennis
On Fri, Dec 24, 2010 at 2:00 PM, Kevin Waddle <thewaddles at sbcglobal.net> wrote:
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