hi i have a table containing
a list of components and the status
table
index, fldcomponnum , fldstatus

the index is an autonumber
the fldcomponnum hold the component number
fldstatus holds the status of the component
the status can be one of three states "Minor", "Offload", "Onload"
the component can have mulitple records,could be anyone one or all the
states and a new entry is created for each state.
i.e
index fldcomponnum fldstatus
1 1234 Offload
2 1234 Onload
3 1234 Minor
4 1234 Offload
5 2365 Offload
6 3365 Offload
7 3365 Onload


what i would like to do is return the last occurr for each record
where the status is offload and it has not been onloaded
so the result woud be

index fldcomponnum fldstatus
4 1234 Offload
5 2365 Offload

index number 7 would be ignored because the component has been
onloaded


any ideas as to the best way to do this

thanks


kevin

You might try the following query. Make sure you have an index on each of the
three fields.

If there is a large number of records, this query may be too slow to be
useful. I used the name ComponentStatus as the table name.

SELECT Max([index]) as HighIndex, fldCompNum, "Offload" as TheStatus
FROM ComponentStatus
WHERE fldStatus = "Offload"
AND Not Exists
(SELECT *
FROM ComponentStatus as Temp
WHERE Temp.fldCompnum = ComponentStatus.fldCompNum
AND Temp.[Index] > ComponentStatus.[Index]
AND Temp.FldStatus = "OnLoad")
GROUP BY fldCompNum

By the way INDEX is not a good field name since Index is a reserved word in
Access. Using reserved words can cause problems unless you surround them with
square brackets.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

kevcar40 wrote:

On 18 Aug, 14:05, John Spencer <JSPEN... (AT) Hilltop (DOT) umbc> wrote:
thank you very much
for your help it works a treat