Getting Last Date

  • Thread starter Thread starter Ramesh
  • Start date Start date
R

Ramesh

HI,

I have a Task table with details of the task like Task ID. There is
another Action table with records of action taken with a ActionDate field.

How can I make a query to get the last action taken date for each Task ID,
keeping the query updateable? The purpose is to look at the last action of
each Task and check a Yes/No field for Task complete in the Task Table.

Without this I am having to go through all the action taken, when I need to
be seeing only the action taken on the latest date for each task.

Thanks for any help.

Ramesh
 
If you don't mind a read-only result, you could use a subquery.
If you have a query into your Task table, you could type something like this
line into a fresh column in the Field row:
LastActionID: ( SELECT TOP 1 Action.ActionID FROM Action
WHERE Action.TaskID = Task.TaskID
ORDERE BY Action.ActionDate DESC, Action.TaskID DESC )

For 3 other alternatives, see:
http://www.mvps.org/access/queries/qry0020.htm
 
HI Allen,

Problem is that I need to update the record in the query.

Thanks very much for your support.

Ramesh
 
Ramesh, are you really sure you want to take on teh responsibility of
*storing* which was the last one, and trying to keep that up to date? Sounds
like a nightmare of trying to manage every insert, edit and delete
(including figuring out what to do when several records are deleted at
once), regardless of whether they occur through forms or action queries, or
....

You would have a difficult time trying to convince me you have a good reason
to break the normalization rules and store this dependent value.
 
Allen,

Sorry I am mailing to your id too cos this is coming in very late. I was
trying out a few other possibilities and have finally come back to this
challenge.

I dont need to store which was the last one. Two things I need from the
last date. One, I need to be able to see the ActionTaken field of the
Lastdate record in the Action Table. Two, I need the sort the JobIDs in the
Job completed order, which is the Last date of each JobID.

CallTable fields - JobID, Callcomplete (Yes/No)
ActionTable fields - JobID, Action Taken, ActionDate

What I want is a query with the JobID and the last ActionTaken listed in the
order of last ActionDate for each JobID. And this is to be an updateable
record, for me to update the Callcomplete field Yes or No.

Or could you suggest any other way I could do this?

Thanks very much
Ramesh
 
The subquery (previous reply) will get you the primary key you need. You
could then use that query as the source for another query, to get the other
field you need as well. Again, results will be read-only.

Another alternative is to use DLookup(). That will not make the rest of your
fields read-only, but it will be very slow if we are talking about a large
recordset here.
 
Back
Top