Access Query

[Frank Spadafora]

I have the following table shown below:

Piece_Operations:

PieceID OperationID Operation StartTime StopTime
=================================================
5 2 B 8:05:07 8:45:11
5 5 A 9:14:01 9:30:37
5 3 C 10:00:12 10:20:05
2 7 B 7:45:44 8:06:23
2 9 C 12:39:00 13:03:12
9 8 A 13:35:55 13:59:55
9 1 C 15:02:02 15:29:59
7 4 C 10:12:37 10:33:32
7 10 A 10:55:01 11:35:42
7 6 B 14:27:22 15:01:02

Is there a way to query this table based on StartTime so
that I get the following results:

PieceID OperationID OperationOrder
2 7 1
2 9 2
5 2 1
5 5 2
5 3 3
7 4 1
7 10 2
7 6 3
9 8 1
9 1 2

In other words, is there any way to generate the Operation Order column
based on the Operation_start_time?

If I was using the data in a report, I could generate the Operation Order by
using an unbound field and setting its Control Source to ‘=1’ and its Running
Sum value to ‘Over Group’.

Since I need to use the Operation Order number in another query, this does
not help me.

Any suggestions would be appreciated.

 

[KARL DEWEY]

Try this --
SELECT Q.PieceID, Q.OperationID, (SELECT COUNT(*) FROM Piece_Operations Q1
WHERE Q1.[PieceID] = Q.[PieceID]
AND Q1.[OperationID] = Q.[OperationID]
AND Q1.[Operation StartTime] < Q.[Operation StartTime])+1 AS
OperationOrder
FROM Piece_Operations AS Q
ORDER BY Q.PieceID, Q.[Operation StartTime];

 

[Michel Walsh]

If there is no dup {pieceID, StartTime} then:


SELECT a.pieceID, LAST(a.operationID), a.startTime, COUNT(*)
FROM tableName AS a INNER JOIN tableName AS b
ON a.pieceID=b.pieceID AND a.startTime >= b.startTime
GROUP BY a.pieceID, a.startTime



Hoping it may help,
Vanderghast, Access MVP