Find Next Related Record

[Snowflake68]

I have a table of jobs that have a sequence of operations in the order that must be carried out. Each operation has a Y/N flag to indicate if it is completed or not.

I am creating a dashboard which shows each Operation on a separate form and a list of jobs currently at that stage of operation.
For each job I need to show the first operation in the sequence that has not yet been completed (thats the easy bit) but also show a column displaying the next operation for that job.

So on the main form I would have for example the two subforms below

Washing
Job Description Qty Next Operation
12345 Text here 10 Hand Press

Drill
Job Description Qty Next Operation
5678 Text Here 14 Etching

The problem; I can find the lowest number in the sequence to get the first operation that hasn’t yet been completed BUT how do I work out the next operation after that?

The operations are numbered sequentially, usually incremented by 10 each time but I need to find a way of working out what is actually the next number in the sequence as sometimes there could be a new operation inserted in between them so I could have one job with operations 10, 20, 30, 40 ,50 (this is not problem because I can do via queries joined together and just add 10 to the sequence number to find the next operation) but this isn't fool proof because another job could have operations which run 10, 20, 25, 26, 28, 30, 40, 50 because new operations have been inserted.

I am using a series of queries to create the columns I need and then subforms to display the data on one large main form. All this works perfectly accept for the Next Operation column when they dont increment by 10 each time.

 

[plog]

You haven't provided what your operation table structure is (I pray its a table and not tables), so I can only give you general advice.

The simplest way to achieve this is to build a query, then make a calculated field with a DMin (https://www.techonthenet.com/access/functions/domain/dmin.php). That DMin would find the lowest value that is higher than the current record. Something like:

NextOperationNumber: DMin("[OperationNumber]", "YourTableNameHere", "[OperationNumber]>" & [OperationNumber])

Most likely the criteria argument of that will include more criteria to keep it within the same project/sequence/semantic equivalent.

 

[jdraw]

snowflake,
It might be helpful if you could show us
-your tables and relationships (relationship window with all tables showing all fields)
-a sample of the query sql that is problematic.

For clarity could you show some sample query/sql and also describe what you want that code to do?

 

[Snowflake68]

You haven't provided what your operation table structure is (I pray its a table and not tables), so I can only give you general advice.

The simplest way to achieve this is to build a query, then make a calculated field with a DMin (https://www.techonthenet.com/access/functions/domain/dmin.php). That DMin would find the lowest value that is higher than the current record. Something like:

NextOperationNumber: DMin("[OperationNumber]", "YourTableNameHere", "[OperationNumber]>" & [OperationNumber])

Most likely the criteria argument of that will include more criteria to keep it within the same project/sequence/semantic equivalent.

It was just the one table so all good there. I have been able to sort this out simply by using the DMin function that you provided. Thanks for providing such a simple solution. I hadnt heard of this one before so I have learned something new

 

[Snowflake68]

snowflake,
It might be helpful if you could show us
-your tables and relationships (relationship window with all tables showing all fields)
-a sample of the query sql that is problematic.

For clarity could you show some sample query/sql and also describe what you want that code to do?

Thanks for your post but I have managed to sort this out using the DMin function provided by 'plog'.