Filtering Duplicate(s)

[chazgaz]

I am looking for a way to filter a field (Batch Name) with duplicate entries based on criteria involving another field in my query.

My database captures the time mail was received (in this case lets say 11:30) and a start time and an end time for various batches of documents being indexed (batches are created from received mail). What can happen is that a batch of 100 documents can be started but then interuppted requiring completion later. For example, an employee might open a batch of 100 documents at 7:45 and index 45 documents and stop at 8:10. The remaining 55 documents may be started at 8:30 and finished at 8:55. The result in my current query displays the same batch name with two separate start times and end times.

What I am trying to do is come up with a cycle time from when the mail was received to when the batch was completed. In my example I would want my query to filter out the 7:45 to 8:10 entry and only calculate my cycle time based on the finished time of 8:55 on my second entry. Right now I am getting two cycle times for the same batch.

Here is my current sqlview...

SELECT DISTINCTROW TeamWeb_Buckets.Recd_Date, TeamWeb_Buckets.BucketID, dbo_LogTable.EmpType, dbo_LogTable.BatchName, dbo_LogTable.TimeStarted, dbo_LogTable.TimeEnded, TimeValue(dbo_LogTable!TimeEnded) AS [End Time], ([ScanDate] & " " & [End Time]) AS [Batch End Time], DateDiff("n",[Recd_Date],[Batch End Time])/1440 AS [Elapsed Time], IIf(dbo_BucketBatches!Archiving=1,"Early Archiving","Late Archiving") AS [Early/Late], dbo_LogTable.Dept
FROM dbo_AutoBucket, TeamWeb_Buckets INNER JOIN (dbo_Prep INNER JOIN (dbo_Employees INNER JOIN ((dbo_Dept INNER JOIN dbo_BucketBatches ON dbo_Dept.deptID = dbo_BucketBatches.Dept) INNER JOIN dbo_LogTable ON (dbo_BucketBatches.Dept = dbo_LogTable.Dept) AND (dbo_BucketBatches.BatchName = dbo_LogTable.BatchName)) ON dbo_Employees.Name = dbo_LogTable.EmpName) ON dbo_Prep.BucketID = dbo_BucketBatches.BucketID) ON TeamWeb_Buckets.BucketID = dbo_Prep.BucketID
WHERE (((dbo_LogTable.EmpType)="INDEXER"))
ORDER BY TeamWeb_Buckets.Recd_Date DESC;

I am new to Access...Hopefully someone can help. Please let me know if clarification is needed.

Thank you

 

[scriverb]

I think what you should initiate the Totals row and Group By all fields except the Finished Date. Here you would use the Min and Max aggregate functions to calculate your time for the Beginning and Ending processing of the Batch.

Post back if you have any questions or need help setting this up.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[lespaul]

So what you want is the minimum start time for each batch and the maximum end time for each batch?

The first thing I notice about your query is that you are selecting from dbo_AutoBucket but none of your fields or joins goes to that table (so I would take that out!).

You'll probably need to have a correlated subquery (I'm not real good at translating existing queries, so I'll give you an example and you should be able to adjust it).

So, BatchTable has the BatchInformation and TimeTable has the different times:

BatchTable
BatchID = 1

TimeTable
(First Entry)
BatchID = 1
StartTIme = 7:15 am
EndTime = 7:45 am
(Second Entry)
BatchID = 1
StartTime = 8:30 am
Endtime = 9:15 am


select a.batchID, Min(a.starttime), Max(b.endtime) FROM
TimeTable A
INNER JOIN TimeTable B on B.BatchID = A.BatchID
Group by a.batchID

This query gets each batchID and the minimum starttime (which will give you the earliest start) and then joins into the same table (named B) and matches the batchID's and finds the one that is the maximum time for that batch.

Hope that's helpful and you can figure out how to use this technique with your query.

leslie

 

[PHV]

Doesn't this do the same ?
SELECT batchID, Min(starttime), Max(endtime)
FROM TimeTable
GROUP BY batchID;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244