Max of one field only if another field has a certain value

[14erclimb]

Hi, what a great resource this forum is! Hopefully you all can help:

I have one field called "OPERATION_NO" another called "WORK_CENTER_NO" and another field called "OPER_STATUS_CODE"
I'm using "2110" as the criteria for "WORK_CENTER_NO"

What I want is to create another field called "CURRENT_WC" where the value will be the "WORK_CENTER_NO" of the highest "OPERATION_NO" that has an "OPER_STATUS_CODE" of "Closed"

I'm going crazy trying to figure this out so hopefully someone a lot smarter than me can help

[JKing]

Hi, what a great resource this forum is! Hopefully you all can help:

I have one field called "OPERATION_NO" another called "WORK_CENTER_NO" and another field called "OPER_STATUS_CODE"
I'm using "2110" as the criteria for "WORK_CENTER_NO"

What I want is to create another field called "CURRENT_WC" where the value will be the "WORK_CENTER_NO" of the highest "OPERATION_NO" that has an "OPER_STATUS_CODE" of "Closed"

I'm going crazy trying to figure this out so hopefully someone a lot smarter than me can help

Are you looking for a query?
Something like this would return the work_center_no that has the highest operation_no and oper_status_code of closed

SELECT Work_center_No
FROM tblYourTable
WHERE operation_no = (SELECT MAX(operation_No) from tblYourTable WHERE oper_status_code = 'closed')
 

[14erclimb]

Are you looking for a query?
Something like this would return the work_center_no that has the highest operation_no and oper_status_code of closed

SELECT Work_center_No
FROM tblYourTable
WHERE operation_no = (SELECT MAX(operation_No) from tblYourTable WHERE oper_status_code = 'closed')
 

I'd like to return the work_center_no (that you provided above) in its own field called "CURRENT_WC" but I want to return this where there is a work_center_no 2110 and oper_status_code <>"Closed" which I already have in the Critera field. Does that make sense?

[JKing]

I'm a little confused.

You want to always show the work_center_no for the highest operation along with the record information where the work_center_no = 2110?

Now you said oper_status_code should be 'closed' in your first post and now you've shown it <> 'closed'?

Is this the kind of output you are looking for?

 work_center_no     operation_no     oper_status_code     wc_current
2110                   10                open               3456
 

[14erclimb]

Yes, that is exactly what I am looking for, where wc current is a closed operation. Sorry for the confusion.

[JKing]

Make two queries. The first being the sql that I posted returning the value of the current_WC.

Base your second query off your table and the previously made query. Select the fields from the table you and then the work_center_no from the previous query and alias it as whatever you want.

Say you saved the first query as qselCurrent_WC your query might look something like this:

SELECT tblYourTable.work_center_no, operation_no, oper_status_code, qselCurrent_WC.work_center_no AS Current_WC
FROM tblYourTable, qselCurrent_WC
WHERE tblYourTable.work_center_no = 2110
 

[14erclimb]

Sorry, I'm new to this. I know I'm close but I'm getting the following error message:

"The specified 'WORK_CENTER_NO' could refer to more than one table listed in the FROM clause of your SQL statement."

Here is the code from the first query:

SELECT IFSAPP_SHOP_ORDER_OPERATION.WORK_CENTER_NO
FROM IFSAPP_SHOP_ORDER_OPERATION
WHERE (((IFSAPP_SHOP_ORDER_OPERATION.OPERATION_NO)=(SELECT MAX(OPERATION_NO) FROM IFSAPP_SHOP_ORDER_OPERATION WHERE OPER_STATUS_CODE = 'Closed')));
 

Here is the code from the second query:

SELECT IFSAPP_SHOP_ORDER_OPERATION.ORDER_NO, WORK_CENTER_NO, OPERATION_NO, OPER_STATUS_CODE, qselCurrent_WC.WORK_CENTER_NO AS Current_WC
FROM IFSAPP_SHOP_ORDER_OPERATION, qselCurrent_WC
WHERE IFSAPP_SHOP_ORDER_OPERATION.WORK_CENTER_NO = 2110
 

[JKing]

You need to reference the table name before the work_center_no field in the SELECT line of your query.

[14erclimb]

I think I got that part figured out. Now when I run the second query it gives me the same Current_WC for every ORDER_NO. I think the first query is returning the max of all operations across all orders, I just want the max operation for each order no. Can I impose on you once more?