Query design to show last stage

[Blings]

Hello everyone,

See my sales order list form design below:-



What I am trying to do is have the above form show the last stage as per the stage junction table. See sales order 1 example with the following stages associated:-



See the query which is loading the form at the top:-





Can anyone suggest how I should modify my existing query to show the latest stage on sales order 1 which is "Despatch" to show on the sales order list form?

[CJ_London]

not quite clear what you want - how does the second image relate to the first? What is sales order 1

Only comment I can make at the moment is last does not mean latest, you need to apply an order which you may or many not be doing. Usually you would use Max

perhaps you just need to apply a criteria to the stageID field for whatever it is for despatch.

[Blings]

Sorry, the very last record from the top image is sales order 1.

Sales order 1 has from the second image a last stage as "Despatch".

I am trying to make the stage control pull the latest stage per order.

[Welshgasman]

Shouldn't you have an order for your status table?

You could use MaxID perhaps, if your autonumber is incrementing?

[Gicu]

You should create another totals query based on tblJxStage table in which you Group By SalesOrderID and get the Max for Stage Date. Call this qryMaxStageDateOrderID.
Now in your existing query bring this new query in and join it on SalesOrderID and StageDate to the tblJxStage and you should have what you want.

Cheers,