Query calculation problem based on 1st and last operation.

[Cam]

Hello,

I am trying to create a query to calculate the flow time for each order, but
not sure what the formula to use. The flow time is the difference between the
completed date and date received (Calculating exclude weekend and holiday).
The problem is there are several operations for each order so for each order,
I need to take the completed date of the last operation minus date received
from the 1st operation. Below is my sample data:

Order Part Operation Date Received Completed Date
1234 119A100 100 3/3/08 3/4/08
1234 119A100 200 3/4/08 3/4/08
1234 119A100 300 3/4/08 3/6/08
1234 119A100 400 3/6/08 3/10/08
1234 119A100 500 3/10/08 3/12/08
2345 200A100 200 2/27/08 3/1/08
2345 200A100 300 3/1/08 3/4/08
2345 200A100 400 3/4/08 3/6/08

Results: with a calculated column
Order Part Start.Oper End.Oper DateReceived ComplDate
Flow Days)
1234 119A100 100 500 3/3/08
3/12/08 5
2345 200A100 200 400 2/27/08 3/6/08
6

NOTE: For order 1234, the CompDate is the date of last operation, 3/12/08.
Can exclude the column for Start.Oper, End.Oper and ComplDate if it is too
complicated.

 

[Allen Browne]

You may be able to do this with a simple Totals query.

1. Create a query using this table.

2. Depress the Total button on the toolbar (upper sigma icon.)
Access adds a Total row to the query design grid.

3. Add Order and Part to the grid.
In the Total row under these fields, accept Group By.

4. Add Operation to the grid.
In the Total, choose Min

5. Add Operation to the grid again.
This time, choose Max under the Total row.

6. Add [Date Received] to the grid.
Choose Min in the Total row.

7. Add [Completed Date] to the grid.
Choose Max.

That may be all you need. However, it does allow for bad data. Say the 2nd
row of your example was entered wrongly, as:
1234 119A100 200 3/4/08 3/4/09
then the max value of the Completed Date would come from this row.