Logic help

[Sam]

I'm working on a small application and I am stuck on the logic behind
one of the queries I want to write. I've always considered myself to be
a good problem solver and it depresses me that I can't figure this out.

I don't really need any SQL syntax or anything, just help with ANDs,
ORs, NOTs, etc...

I have a table that has a column for a specific action. Each row in the
table represents when an action is taken on a particular item. The item
itself can show up numerous times in the table for different actions.

There are four actions that can be taken, and they are sequential. I
won't get into too much detail, so I will just call them A, B, C, and
D. Action A must be taken for a particular ID# before B can be taken.
The program logic itself handles that validation. So, the ID# that has
action C *will* have B and A.

The query I am trying to write is to just list the ID and the latest
action taken. So, ID#1 may have A, B, and C - but I only want to list
C. ID#2 may have A-D, so I'd only list D.

To make matters worse, action C can be taken numerous times. So for one
ID number, you can have multiple Cs (The "action log" uses an
autonumber PK). So in my query, where I'll have an item that is on
action C, I need the *latest* C.

So, in short, I am trying to find a way to get the latest action for a
particular item.

Would it be easiest to perform logic in the query to pull this
information, or should I timestamp each action and just pull the latest
action for each record? Any other suggestions?

[Rog]

If your actions are actually called A, B, C and D, you can create a
"totals" query (click on the sum or totals button). On the total line
select "Group by" for the ID# and "Max" for the action, since B > A
etc.

If the actions have different names you can create a table containing a
record for each of your 4 actions, and give them numbers 1 to 4 (or a
letters A to D like in your example), e.g. ActionName = "Start of
process", ActionNumber = 1 for your first record, etc. In your query,
link your two tables on the action name and use the action number (or
letter) in the Max column.

[Sam]

Thanks, Rog. I think I'm going to take your advice about the table
giving each action a number. I'll try that and tweak things around...
and hopefully everything will work out.

Thanks