[AccessD] Need a summary query to return more than one record for amember of a group

Thanks, Gustav!  That should do the trick.  Never thought of using the
Top N in this way.

Don

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Thursday, November 18, 2004 10:07 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] Need a summary query to return more than one record
for amember of a group


Hi Don
 
You need to, for each record, look up the date/time of the next entry
and calculate the minutes:
 
  lngMinutes: DateDiff("n", InsertTime, (Select Top 1 T.InsertTime From
tblTable As T Where T.InsertTime > tblTable.InsertTime Order By
T.InsertTime Asc)
 
The sum the minutes by grouping in another query where you use the
above as source.
 
/gustav

>>> donald.a.Mcgillivray at mail.sprint.com 18-11-2004 18:11:37 >>>

Hi, All

I have a table of data, populated by an automated mail insertion
system,
containing the following fields:

JobID 
MachineID
OperatorID
PieceID
InsertTime (Date + Time)

The system writes a record for each piece that is inserted by the
system.  Typically, there will be a single operator working a job on a
machine, but on larger jobs an operator's shift may be interrupted by
another operator (relief during breaks, technicians making
adjustments,
repairs, etc.)  When these interruptions occur, the interrupter logs
in
and any work processed during the interruption is logged under the
interrupter's ID.  Then when the original operator returns, s/he logs
in
and subsequent work is logged under that ID.  A summary query grouped
on
JobID, MachineID, and OperatorID, with count of PieceID and min and
max
of InsertTime gives me a single record for each operator showing piece
count and start and end time.  However, the delta between the min and
max times does not reflect the actual elapsed time for each, since the
original operator's min and max times encompass the period when the
interrupter was logged in as well.  

I know I could loop thru the records incrementing counters and write a
record to a table when a change in operator is detected, but my
preference is to capture elapsed run time for each operator using a
single query.  Anybody know if/how I can return more than one record
for
a member of a group in a summary query?

Don McGillivray
Sprint Mailing Services

-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com