Lookups or Calculated field?

[PivotIdiot]

Hi Everyone.
I'm struggling a bit with what i thought would be a fairly simple table of values.

I have a data connection to a table with multiple columns that represent our current live works orders.
The fields i'm interested in are something like:

WorksOrder	Sequence	Department	Duration	Complete
400	1	WELD	20	YES
400	2	FITTING	40	NO
401	1	WELD	30	YES
401	2	MACHINE	60	NO
401	3	PAINT	120	NO
401	4	FITTING	30	NO
402	1	FITTING	10	NO
403	1	FITTING	30	NO
404	1	PAINT	60	NO

I want to show

WorksOrder	Current Seq of Seq's	Department	Seq Duration Left
400	2 of 2	FITTING	40
401	2 of 4	MACHINE	60
402	1 of 1	FITTING	10
403	1 of 1	FITTING	30
404	1 of 1	PAINT	60

I have managed to get the minimum not completed seq of the maximum sequence per works order but i cant for the life of me get the department. No matter what i try the department column expands the rest of the table to show all departments per works order.
Can somebody point me in the right direction please?

 

[RoryA]

You could create a query that just groups by WorksOrder and calculates the min sequence number. Then merge that with your original data on WorksOrder and Sequence using an inner join.

 

[PivotIdiot]

This is exactly what ive just done, came back here to update. Seems to be working wonderfully, thanks for your quick response!