Current record OR last EndDate

[RD]

Hi all,

Ordinarily I can figure this kind of thing out but, I just can't seem
to wrap my head around this one.

I have a table that tracks clients activities. It has ClientID,
ActivityID, Start Date and EndDate along with a slew of other columns
(I didn't create this POS). I need to return the CURRENT record
(defined as EndDate is null) or, if there is no null EndDate for a
ClientID, the latest EndDate.

To restate, I need to return a row for every ClientID showing either a
null EndDate OR, if no Null EndDate then, the latest EndDate.

Here's the kicker, and where all my attempts have failed, I can't have
two records for a ClientID, one WITH an EndDate AND one with a null
EndDate. I absolutely need one record per ClientID.

This recordset is the source of a form that is actually pretty
important to a particular business process. I'm open to either just a
query or a vba solution.

So, any ideas?

Thanks,
RD

 

[golfinray]

Use a max function. Push the summation button, the button on your toolbar
that looks like an E, and change the group by on enddate to max.

 

[RD]

I appreciate you trying to help but if it were that easy I wouldn't
have posted the question. Doing a simple Max on EndDate returns
multiple rows.

 

[Lotta]

Hi RD,

I have a similar table for team membership, and sometimes need to determine
what the current team is for each person.

I build a query to pull the person's information & team name from the table
using two rows of criteria (so that the query uses either row 1 or row 2 for
qualifying the data).

Row 1 uses criteria of: StartDate <= Date() and EndDate > Date().
Note: I never have their membership start & end on the same day.

Row 2 uses criteria of: StartDate <= Date() and EndDate Is Null.

I use something similar for team membership on a specific date. I just
substitute a reference to a field on a form where the date is entered instead
of using Date().

Hope this helps you...

Lotta

 

[John Spencer]

Oe method that should work is to use two queries.

Query one saved as QueryOne

SELECT ClientID, Max(Nz(EndDate,#1/1/9999#)) as theEnd
FROM ClientActivities

Query two:
SELECT ClientActivities.*
FROM ClientActivities INNER JOIN queryOne
ON Nz(ClientActivities.EndDate,#1/1/9999#) = QueryOne.TheEnd


OR another alternative is to use a correlated subquery
SELECT C.*
FROM ClientActivities as C
WHERE Nz(EndDate,#1/1/9999# IN
(SELECT Top 1 Nz(EndDate,#1/1/9999#)
FROM ClientActivities as C1
WHERE C1.ClientID = C.ClientID
ORDER BY EndDate is Null, EndDate DESC)

Make sure you have indexes on EndDate and ClientID or this could be
agonizingly slow.

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

 

[John Spencer MVP]

Whoops! missed a closing parentheses in the query using the correlated subquery.


SELECT C.*
FROM ClientActivities as C
WHERE Nz(EndDate,#1/1/9999#) IN
(SELECT Top 1 Nz(EndDate,#1/1/9999#)
FROM ClientActivities as C1
WHERE C1.ClientID = C.ClientID
ORDER BY EndDate is Null, EndDate DESC)

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

 

[RD]

Thanks John. Looks like that's going to do it. You're right about it
being a bit slow. As soon as I can get all the users off this thing
I'll set an index on EndDate (ClientID is already indexed).

Can you explain the "EndDate Is Null" in the ORDER BY clause? I've
never seen that before.

Thanks,
RD

 

[John Spencer MVP]

EndDate is Null will return True (-1) or False (0) and the sort will take
place based on that.

You could use
ORDER BY Nz(EndDate,#1/1/9999#) DESC
and get the same results.

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

 

[RD]

I see. That's pretty cool.

Thanks for the info and the query.

RD