Showing the most recent record for each client

[jeff klein]

Below is a query that shows "CarePlans" for all active customers. It only
shows careplans that exist between "MonthStartDate" and "MonthEndDate" which
Is entered during query execution.

What I also need to the query to show:
Only show the most recent care plan for each customer (presently is shows
all careplans for each customer)
Also show new careplans that fall between the "MonthStartDate" and
"MonthEndDate"

Note: The NZ function is used because noaend field frequently is not entered
(null)

PARAMETERS [MonthStartDate] DateTime, [MonthEndDate] DateTime;
SELECT [ClientLastName] & ", " & [ClientFirstName] AS ClientLastandFirst,
CarePlans.SOCStart, CarePlans.NOAEnd, nz([noaend],#12/31/9999#) AS
ActualNOAEnd
FROM Clients INNER JOIN CarePlans ON Clients.ClientID = CarePlans.ClientID
WHERE (((CarePlans.SOCStart)<=[MonthStartDate]) AND
((nz([noaend],#12/31/9999#))>=[MonthStartDate]) AND ((Clients.Inactive)=0))
ORDER BY [ClientLastName] & ", " & [ClientFirstName];

 

[Jeff Boyce]

Jeff

Your requirements seem to contradict:

" Only show the most recent care plan for each customer (presently is shows

all careplans for each customer)"

vs.
"Also show new careplans that fall between the "MonthStartDate" and

 

[Jeff Klein]

Yes...the data desired is the latest careplan at the beginning of a given
month and also any new careplans that have come in during the month.

 

[Jeff Boyce]

Jeff

So, are you saying that you want the first careplan of any given month, plus
any that occur after?

If that is the case, how is that different from selecting ALL careplans in
that given month?

Still seeking more information...

Jeff Boyce
<Access MVP>

 

[Jeff Klein]

I am creating a report that prints the careplan that is active at the
beginning of the month in addition the careplan that supersedes it.

 

[Jeff Klein]

The problem with reporting all within a given month is that with NOAEnd
being null on many records it will show all the old records just because
there is no entry. This is why I want to show the latest for the beginning
and then any new that show up during the month. Clear as MUD huh!

 

[Jeff Boyce]

Jeff

I must be slow. I'm still not getting what you're trying to find. Perhaps
if you provided an example ...?

 

[John Spencer (MVP)]

Pardon me,

I think that what the OP is seeking is the latest care plan that occurred on or
before the first day of the month, plus any other care plans that have
originated in the month. Of course, the possibility exists that the OP wants
only the latest care plan of the second group also.

So with the following dates for one individual

Jun 14, 2004
Jun 28, 2004
July 5, 2004
July 12, 2004

The OP wants either
Jun 28, 2004
July 5, 2004
July 12, 2004

OR

Jun 28, 2004
July 12, 2004

That is my reading of what the OP is asking for.

 

[Jeff Klein]

Sorry about the delay in my response...

the correct result is
Jun 28, 2004
July 5, 2004
July 12, 2004