Identify Last occurance

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I can do a little VB and have a reasonable grasp of Access but I have a
problem which I'm not even sure how to tackle!!

My database has a table of Projects, their status and a table of how the
status has changed over the year. Each status change has a uniquie ID, so if
a project has a number of status changes in the year the latest change would
have the highest ID for a particular project. As the database is an actually
an export from an externally produced system, to confuse matters users can
actually pass through the same status several times, but my aim is still the
same - to identify the records which have the last occurance of a particualr
status.

For example:

Status Change ID Project Status Last4?
1 a 1 n
2 a 4 y
3 a 3 n
7 b 1 n
9 b 4 n
15 b 4 y
18 b 3 n

In the above example I would be seeking to flag up Status Change ID record 2
and 15 as these are the highest occurance of status change 4. (If that makes
sense).

At the moment I have the above table in a query, so either I could build an
expression that put a flag in the records I want, or I could do something in
VB. But I thought the Programming forum would be best to raise this in.

Any pointers on how I show tackle this would be much appreciated.

many thanks

Winger
 
As per my question above, I'd like to use "Last of" or "Highest" in the query
design, if there was a way of avoiding using VB. This would mean I could
perhaps use an expression along the lines of "If status change ID is highest
occruance for the selected project and where status = 4 , set flag"

But I don't know how to build this in the query.

thanks
 
Back
Top