DMax alternative?

[Ken Snell]

Let's try this approach first:

SELECT CarePlans.*
FROM CarePlans
WHERE CarePlans.SOCStart IN
(SELECT MAX(T.SOCStart)
FROM CarePlans AS T
WHERE T.ClientID=CarePlans.ClientID);


--

Ken Snell
<MS ACCESS MVP>

access 97

As asked elsethread, which ACCESS version are you using? The suggestion that
is in that other thread (using the [ ]. syntax) is something that ACCESS
does automatically in some queries when you save and close them. Can be a
real problem to maintain, as once saved, ACCESS won't let you edit them
easily. In those case, then I use a separate query that is the subquery, and
then join that query as a source table in the original query.

Post back with info and we'll see where we can go with this.

--

Ken Snell
<MS ACCESS MVP>

Syntax error is in the FROM clause

Looks ok to me...what syntax error does it give? and, assuming that you're
doing this in SQL view, where does the cursor "go" to show you where the
error is?

--

Ken Snell
<MS ACCESS MVP>

SELECT CarePlans.*
FROM CarePlans
INNER JOIN
(SELECT T.ClientID, MAX(T.SOCStart) as MostRecent
FROM CarePlans T
GROUP BY T.ClientID) as T1
ON CarePlans.ClientID = T1.ClientID
AND CarePlans.SOCStart = T1.MostRecent;


Post the actual SQL that is giving you the syntax error.

--

Ken Snell
<MS ACCESS MVP>

I am getting a syntax error in the qry_MostRecentCarePlan. I have
been
trying to figure it out but I am stumped on the subquery and

the
use

of
the
"T".


Jeff,

Does your CarePlans table contain an ID field?

Alright, Create a new query (call it qry_MostRecentCarePlan)

SELECT CarePlans.*
FROM CarePlans
INNER JOIN
(SELECT T.ClientID, MAX(T.SOCStart) as MostRecent
FROM CarePlans T
GROUP BY T.ClientID) as T1
ON CarePlans.ClientID = T1.ClientID
AND CarePlans.SOCStart = T1.MostRecent

This query should give you a single record for each client, and
all
the
information will be from the most recent careplan for that
individual.
(assuming that SOCStart is the field that indicates the most
recent
plan.

Now, replace references to the Careplan table in your query with
qry_MostRecentCarePlan, and you should have what you are looking
for.

HTH
Dale

OK...The code I have sent is an example that I created to keep
things
simple
as the real code is much more complicated. You clearly know
enough
to
solve
my problem although I am having trouble using the answer you
have
given
and
relating back to the original code. Below is the original code.
This
shows
many records for each client. I just want the latest (or last)
for
each
client. Sorry about the confusion but I try not to put

too
much

into
the
message board as it might not be answered. Also, for me, this
is
hard
to
explain thoroughly. Any help is greatly appreciated and

if
you

choose
not
to spend any more time on this I will understand. Thanks Jeff.



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))
OR
(((CarePlans.SOCStart)>[MonthStartDate] And
(CarePlans.SOCStart)<=[MonthEndDate]))
ORDER BY [ClientLastName] & ", " & [ClientFirstName];



T is an alias for the table Orders. You need to differentiate
between
the
two copies of Orders table when you use a subquery.

I also note that I mistyped ClientID in the last line...left
out
the
"t"
by
accident.

--

Ken Snell
<MS ACCESS MVP>

message
Thanks for the reply. I am trying to make this work

What

is
"T"
?

message
You can replace it with a subquery:

(SELECT Max(T.Orders) FROM
Orders AS T WHERE
T.ClientID = Orders.ClienID)

--

Ken Snell
<MS ACCESS MVP>

in

message
I am using DMax to show the last "SOCStart" for all
customers.
This
function makes the query run very slow. Is there
another
way
to
do
this
that might run faster? Below is the code example.
Thank
you.


DMax("[SOCStart]","[Orders]","[Orders].[ClientID]

=

"

&
[Orders].[ClientID])

 

[Jeff Klein]

OK...This appears to give the latest care plan for each client.
I am curious how it handles the null NOAEnd.

Let's try this approach first:

SELECT CarePlans.*
FROM CarePlans
WHERE CarePlans.SOCStart IN
(SELECT MAX(T.SOCStart)
FROM CarePlans AS T
WHERE T.ClientID=CarePlans.ClientID);


--

Ken Snell
<MS ACCESS MVP>

access 97

As asked elsethread, which ACCESS version are you using? The

suggestion
that

is in that other thread (using the [ ]. syntax) is something that ACCESS
does automatically in some queries when you save and close them. Can

be

a

real problem to maintain, as once saved, ACCESS won't let you edit them
easily. In those case, then I use a separate query that is the

subquery,
and

then join that query as a source table in the original query.

Post back with info and we'll see where we can go with this.

--

Ken Snell
<MS ACCESS MVP>


Syntax error is in the FROM clause

Looks ok to me...what syntax error does it give? and, assuming that
you're
doing this in SQL view, where does the cursor "go" to show you

where
the

error is?

--

Ken Snell
<MS ACCESS MVP>

SELECT CarePlans.*
FROM CarePlans
INNER JOIN
(SELECT T.ClientID, MAX(T.SOCStart) as MostRecent
FROM CarePlans T
GROUP BY T.ClientID) as T1
ON CarePlans.ClientID = T1.ClientID
AND CarePlans.SOCStart = T1.MostRecent;


Post the actual SQL that is giving you the syntax error.

--

Ken Snell
<MS ACCESS MVP>

I am getting a syntax error in the qry_MostRecentCarePlan.

I
have

been
trying to figure it out but I am stumped on the subquery and the
use
of
the
"T".


Jeff,

Does your CarePlans table contain an ID field?

Alright, Create a new query (call it qry_MostRecentCarePlan)

SELECT CarePlans.*
FROM CarePlans
INNER JOIN
(SELECT T.ClientID, MAX(T.SOCStart) as MostRecent
FROM CarePlans T
GROUP BY T.ClientID) as T1
ON CarePlans.ClientID = T1.ClientID
AND CarePlans.SOCStart = T1.MostRecent

This query should give you a single record for each

client,
and

all
the
information will be from the most recent careplan for that
individual.
(assuming that SOCStart is the field that indicates the most
recent
plan.

Now, replace references to the Careplan table in your

query
with

qry_MostRecentCarePlan, and you should have what you are looking
for.

HTH
Dale

OK...The code I have sent is an example that I created

to
keep

things
simple
as the real code is much more complicated. You clearly know
enough
to
solve
my problem although I am having trouble using the answer you
have
given
and
relating back to the original code. Below is the original
code.
This
shows
many records for each client. I just want the latest (or
last)
for
each
client. Sorry about the confusion but I try not to put too
much
into
the
message board as it might not be answered. Also, for

me,
this

is
hard
to
explain thoroughly. Any help is greatly appreciated and if
you
choose
not
to spend any more time on this I will understand. Thanks Jeff.



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))
OR
(((CarePlans.SOCStart)>[MonthStartDate] And
(CarePlans.SOCStart)<=[MonthEndDate]))
ORDER BY [ClientLastName] & ", " & [ClientFirstName];



message
T is an alias for the table Orders. You need to
differentiate
between
the
two copies of Orders table when you use a subquery.

I also note that I mistyped ClientID in the last line...left
out
the
"t"
by
accident.

--

Ken Snell
<MS ACCESS MVP>

message
Thanks for the reply. I am trying to make this work What
is
"T"
?

message
You can replace it with a subquery:

(SELECT Max(T.Orders) FROM
Orders AS T WHERE
T.ClientID = Orders.ClienID)

--

Ken Snell
<MS ACCESS MVP>

in
message
I am using DMax to show the last "SOCStart" for all
customers.
This
function makes the query run very slow. Is there
another
way
to
do
this
that might run faster? Below is the code example.
Thank
you.

DMax("[SOCStart]","[Orders]","[Orders].[ClientID]

=

"
&
[Orders].[ClientID])

 

[Jeff Klein]

The problem I see with the latest record is that I might need to show
records for a month that has past.

Let's try this approach first:

SELECT CarePlans.*
FROM CarePlans
WHERE CarePlans.SOCStart IN
(SELECT MAX(T.SOCStart)
FROM CarePlans AS T
WHERE T.ClientID=CarePlans.ClientID);


--

Ken Snell
<MS ACCESS MVP>

access 97

As asked elsethread, which ACCESS version are you using? The

suggestion
that

is in that other thread (using the [ ]. syntax) is something that ACCESS
does automatically in some queries when you save and close them. Can

be

a

real problem to maintain, as once saved, ACCESS won't let you edit them
easily. In those case, then I use a separate query that is the

subquery,
and

then join that query as a source table in the original query.

Post back with info and we'll see where we can go with this.

--

Ken Snell
<MS ACCESS MVP>


Syntax error is in the FROM clause

Looks ok to me...what syntax error does it give? and, assuming that
you're
doing this in SQL view, where does the cursor "go" to show you

where
the

error is?

--

Ken Snell
<MS ACCESS MVP>

SELECT CarePlans.*
FROM CarePlans
INNER JOIN
(SELECT T.ClientID, MAX(T.SOCStart) as MostRecent
FROM CarePlans T
GROUP BY T.ClientID) as T1
ON CarePlans.ClientID = T1.ClientID
AND CarePlans.SOCStart = T1.MostRecent;


Post the actual SQL that is giving you the syntax error.

--

Ken Snell
<MS ACCESS MVP>

I am getting a syntax error in the qry_MostRecentCarePlan.

I
have

been
trying to figure it out but I am stumped on the subquery and the
use
of
the
"T".


Jeff,

Does your CarePlans table contain an ID field?

Alright, Create a new query (call it qry_MostRecentCarePlan)

SELECT CarePlans.*
FROM CarePlans
INNER JOIN
(SELECT T.ClientID, MAX(T.SOCStart) as MostRecent
FROM CarePlans T
GROUP BY T.ClientID) as T1
ON CarePlans.ClientID = T1.ClientID
AND CarePlans.SOCStart = T1.MostRecent

This query should give you a single record for each

client,
and

all
the
information will be from the most recent careplan for that
individual.
(assuming that SOCStart is the field that indicates the most
recent
plan.

Now, replace references to the Careplan table in your

query
with

qry_MostRecentCarePlan, and you should have what you are looking
for.

HTH
Dale

OK...The code I have sent is an example that I created

to
keep

things
simple
as the real code is much more complicated. You clearly know
enough
to
solve
my problem although I am having trouble using the answer you
have
given
and
relating back to the original code. Below is the original
code.
This
shows
many records for each client. I just want the latest (or
last)
for
each
client. Sorry about the confusion but I try not to put too
much
into
the
message board as it might not be answered. Also, for

me,
this

is
hard
to
explain thoroughly. Any help is greatly appreciated and if
you
choose
not
to spend any more time on this I will understand. Thanks Jeff.



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))
OR
(((CarePlans.SOCStart)>[MonthStartDate] And
(CarePlans.SOCStart)<=[MonthEndDate]))
ORDER BY [ClientLastName] & ", " & [ClientFirstName];



message
T is an alias for the table Orders. You need to
differentiate
between
the
two copies of Orders table when you use a subquery.

I also note that I mistyped ClientID in the last line...left
out
the
"t"
by
accident.

--

Ken Snell
<MS ACCESS MVP>

message
Thanks for the reply. I am trying to make this work What
is
"T"
?

message
You can replace it with a subquery:

(SELECT Max(T.Orders) FROM
Orders AS T WHERE
T.ClientID = Orders.ClienID)

--

Ken Snell
<MS ACCESS MVP>

in
message
I am using DMax to show the last "SOCStart" for all
customers.
This
function makes the query run very slow. Is there
another
way
to
do
this
that might run faster? Below is the code example.
Thank
you.

DMax("[SOCStart]","[Orders]","[Orders].[ClientID]

=

"
&
[Orders].[ClientID])

 

[Ken Snell]

Sorry....got so deep into the thread that I'd forgotten the previous
starting point.

What is the NOAend field ? You want it to be Null in the query that returns
the max value? Or somewhere else?

While I wait for that clarification, if you want to filter the "max" date:

SELECT CarePlans.*
FROM CarePlans
WHERE CarePlans.SOCStart IN
(SELECT MAX(T.SOCStart)
FROM CarePlans AS T
WHERE T.ClientID=CarePlans.ClientID
--

Ken Snell
<MS ACCESS MVP>

AND T.SOCStart <= "#" &
Format([Enter the maximum date:], "mm/dd/yyyy") & "#");

OK...This appears to give the latest care plan for each client.
I am curious how it handles the null NOAEnd.

Let's try this approach first:

SELECT CarePlans.*
FROM CarePlans
WHERE CarePlans.SOCStart IN
(SELECT MAX(T.SOCStart)
FROM CarePlans AS T
WHERE T.ClientID=CarePlans.ClientID);


--

Ken Snell
<MS ACCESS MVP>

access 97


As asked elsethread, which ACCESS version are you using? The suggestion
that
is in that other thread (using the [ ]. syntax) is something that ACCESS
does automatically in some queries when you save and close them. Can

be

a
real problem to maintain, as once saved, ACCESS won't let you edit them
easily. In those case, then I use a separate query that is the subquery,
and
then join that query as a source table in the original query.

Post back with info and we'll see where we can go with this.

--

Ken Snell
<MS ACCESS MVP>


Syntax error is in the FROM clause

Looks ok to me...what syntax error does it give? and, assuming that
you're
doing this in SQL view, where does the cursor "go" to show you where
the
error is?

--

Ken Snell
<MS ACCESS MVP>

SELECT CarePlans.*
FROM CarePlans
INNER JOIN
(SELECT T.ClientID, MAX(T.SOCStart) as MostRecent
FROM CarePlans T
GROUP BY T.ClientID) as T1
ON CarePlans.ClientID = T1.ClientID
AND CarePlans.SOCStart = T1.MostRecent;


Post the actual SQL that is giving you the syntax error.

--

Ken Snell
<MS ACCESS MVP>

I am getting a syntax error in the qry_MostRecentCarePlan. I
have
been
trying to figure it out but I am stumped on the subquery

and
the

use
of
the
"T".


Jeff,

Does your CarePlans table contain an ID field?

Alright, Create a new query (call it qry_MostRecentCarePlan)

SELECT CarePlans.*
FROM CarePlans
INNER JOIN
(SELECT T.ClientID, MAX(T.SOCStart) as MostRecent
FROM CarePlans T
GROUP BY T.ClientID) as T1
ON CarePlans.ClientID = T1.ClientID
AND CarePlans.SOCStart = T1.MostRecent

This query should give you a single record for each client,
and
all
the
information will be from the most recent careplan for that
individual.
(assuming that SOCStart is the field that indicates the most
recent
plan.

Now, replace references to the Careplan table in your query
with
qry_MostRecentCarePlan, and you should have what you are
looking
for.

HTH
Dale

message
OK...The code I have sent is an example that I created to
keep
things
simple
as the real code is much more complicated. You

clearly
know

enough
to
solve
my problem although I am having trouble using the

answer
you

have
given
and
relating back to the original code. Below is the original
code.
This
shows
many records for each client. I just want the latest (or
last)
for
each
client. Sorry about the confusion but I try not to

put
too

much
into
the
message board as it might not be answered. Also, for me,
this
is
hard
to
explain thoroughly. Any help is greatly appreciated

and
if

you
choose
not
to spend any more time on this I will understand. Thanks
Jeff.



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))
OR
(((CarePlans.SOCStart)>[MonthStartDate] And
(CarePlans.SOCStart)<=[MonthEndDate]))
ORDER BY [ClientLastName] & ", " & [ClientFirstName];



message
T is an alias for the table Orders. You need to
differentiate
between
the
two copies of Orders table when you use a subquery.

I also note that I mistyped ClientID in the last
line...left
out
the
"t"
by
accident.

--

Ken Snell
<MS ACCESS MVP>

in

message
Thanks for the reply. I am trying to make this work
What
is
"T"
?

"Ken Snell" <[email protected]>

wrote
in

message
You can replace it with a subquery:

(SELECT Max(T.Orders) FROM
Orders AS T WHERE
T.ClientID = Orders.ClienID)

--

Ken Snell
<MS ACCESS MVP>

in
message
I am using DMax to show the last "SOCStart"

for
all

customers.
This
function makes the query run very slow. Is there
another
way
to
do
this
that might run faster? Below is the code example.
Thank
you.

DMax("[SOCStart]","[Orders]","[Orders].[ClientID]

=
"
&
[Orders].[ClientID])

 

[Jeff Klein]

Again..Thank you for the help. A little background. Our clients have NOA's
that hold information on services that they require. A client may have a
NOA that exists from 1-1-04(NOAStart) thru 4-15-04(NOAEnd) or sometimes the
NOAEnd is not entered (null) because the NOA is ongoing and the end is to be
determined at a later date.

The query you gave me works great and is much faster than the original one I
had that used DMax. Although my original "DMAX" query, and yours, only
returnes the latest NOAs. If I was returning NOA's for right now (on this
date) the one you gave me is the correct query. I am trying to improve the
query and return NOAs for particular dates. For example, if I needed the
NOAs that apply for 6-1-02 the "latest" type query would not be correct.

So, I need to return NOAs that are existing between a specified range
[MonthStart] and [MonthEnd], find the latest record within the specified
range, and also return any new records that come in during the range.

Example:
John smith : 1-1-02 thru 12-31-02
John smith : 1-1-03 thru null
John smith : 6-1-03 thru 12-31-03
John smith : 1-1-04 thru null
John smith : 2-6-04 thru 12-1-04
John smith : 8-1-04 thru null

Query for 2-1-04 thru 2-29-04 shows
John smith : 1-1-04 thru null
John smith : 2-6-04 thru 12-1-04

Sorry....got so deep into the thread that I'd forgotten the previous
starting point.

What is the NOAend field ? You want it to be Null in the query that returns
the max value? Or somewhere else?

While I wait for that clarification, if you want to filter the "max" date:

SELECT CarePlans.*
FROM CarePlans
WHERE CarePlans.SOCStart IN
(SELECT MAX(T.SOCStart)
FROM CarePlans AS T
WHERE T.ClientID=CarePlans.ClientID
--

Ken Snell
<MS ACCESS MVP>

AND T.SOCStart <= "#" &
Format([Enter the maximum date:], "mm/dd/yyyy") & "#");

OK...This appears to give the latest care plan for each client.
I am curious how it handles the null NOAEnd.

Let's try this approach first:

SELECT CarePlans.*
FROM CarePlans
WHERE CarePlans.SOCStart IN
(SELECT MAX(T.SOCStart)
FROM CarePlans AS T
WHERE T.ClientID=CarePlans.ClientID);


--

Ken Snell
<MS ACCESS MVP>


access 97


As asked elsethread, which ACCESS version are you using? The suggestion
that
is in that other thread (using the [ ]. syntax) is something that ACCESS
does automatically in some queries when you save and close them.

Can
be

a
real problem to maintain, as once saved, ACCESS won't let you edit them
easily. In those case, then I use a separate query that is the subquery,
and
then join that query as a source table in the original query.

Post back with info and we'll see where we can go with this.

--

Ken Snell
<MS ACCESS MVP>


Syntax error is in the FROM clause

Looks ok to me...what syntax error does it give? and, assuming that
you're
doing this in SQL view, where does the cursor "go" to show you where
the
error is?

--

Ken Snell
<MS ACCESS MVP>

SELECT CarePlans.*
FROM CarePlans
INNER JOIN
(SELECT T.ClientID, MAX(T.SOCStart) as MostRecent
FROM CarePlans T
GROUP BY T.ClientID) as T1
ON CarePlans.ClientID = T1.ClientID
AND CarePlans.SOCStart = T1.MostRecent;


Post the actual SQL that is giving you the syntax error.

--

Ken Snell
<MS ACCESS MVP>

I am getting a syntax error in the

qry_MostRecentCarePlan.
I

have
been
trying to figure it out but I am stumped on the subquery and
the
use
of
the
"T".


Jeff,

Does your CarePlans table contain an ID field?

Alright, Create a new query (call it
qry_MostRecentCarePlan)

SELECT CarePlans.*
FROM CarePlans
INNER JOIN
(SELECT T.ClientID, MAX(T.SOCStart) as MostRecent
FROM CarePlans T
GROUP BY T.ClientID) as T1
ON CarePlans.ClientID = T1.ClientID
AND CarePlans.SOCStart = T1.MostRecent

This query should give you a single record for each client,
and
all
the
information will be from the most recent careplan for that
individual.
(assuming that SOCStart is the field that indicates

the
most

recent
plan.

Now, replace references to the Careplan table in your query
with
qry_MostRecentCarePlan, and you should have what you are
looking
for.

HTH
Dale

message
OK...The code I have sent is an example that I

created
to

keep
things
simple
as the real code is much more complicated. You clearly
know
enough
to
solve
my problem although I am having trouble using the answer
you
have
given
and
relating back to the original code. Below is the original
code.
This
shows
many records for each client. I just want the

latest
(or

last)
for
each
client. Sorry about the confusion but I try not to put
too
much
into
the
message board as it might not be answered. Also,

for
me,

this
is
hard
to
explain thoroughly. Any help is greatly appreciated and
if
you
choose
not
to spend any more time on this I will understand. Thanks
Jeff.



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))
OR
(((CarePlans.SOCStart)>[MonthStartDate] And
(CarePlans.SOCStart)<=[MonthEndDate]))
ORDER BY [ClientLastName] & ", " & [ClientFirstName];



message
T is an alias for the table Orders. You need to
differentiate
between
the
two copies of Orders table when you use a subquery.

I also note that I mistyped ClientID in the last
line...left
out
the
"t"
by
accident.

--

Ken Snell
<MS ACCESS MVP>

in
message
Thanks for the reply. I am trying to make this work
What
is
"T"
?

in
message
You can replace it with a subquery:

(SELECT Max(T.Orders) FROM
Orders AS T WHERE
T.ClientID = Orders.ClienID)

--

Ken Snell
<MS ACCESS MVP>

"jeff klein"

wrote
in
message
I am using DMax to show the last "SOCStart" for
all
customers.
This
function makes the query run very slow. Is there
another
way
to
do
this
that might run faster? Below is the code example.

Thank
you.

DMax("[SOCStart]","[Orders]","[Orders].[ClientID]
=
"
&
[Orders].[ClientID])

 

[Ken Snell]

OK - let's try this:

SELECT CarePlans.*
FROM CarePlans
WHERE CarePlans.SOCStart IN
(SELECT MAX(T.SOCStart)
FROM CarePlans AS T
WHERE T.ClientID=CarePlans.ClientID
AND T.SOCStart <= "#" &
Format([Enter the maximum date:], "mm/dd/yyyy") & "#"
AND T.SOCStart <= "#" &
Format([Enter the maximum NOA date:], "mm/dd/yyyy") & "#");

I think this is headed in the direction you want to go?

--

Ken Snell
<MS ACCESS MVP>

Again..Thank you for the help. A little background. Our clients have NOA's
that hold information on services that they require. A client may have a
NOA that exists from 1-1-04(NOAStart) thru 4-15-04(NOAEnd) or sometimes the
NOAEnd is not entered (null) because the NOA is ongoing and the end is to be
determined at a later date.

The query you gave me works great and is much faster than the original one I
had that used DMax. Although my original "DMAX" query, and yours, only
returnes the latest NOAs. If I was returning NOA's for right now (on this
date) the one you gave me is the correct query. I am trying to improve the
query and return NOAs for particular dates. For example, if I needed the
NOAs that apply for 6-1-02 the "latest" type query would not be correct.

So, I need to return NOAs that are existing between a specified range
[MonthStart] and [MonthEnd], find the latest record within the specified
range, and also return any new records that come in during the range.

Example:
John smith : 1-1-02 thru 12-31-02
John smith : 1-1-03 thru null
John smith : 6-1-03 thru 12-31-03
John smith : 1-1-04 thru null
John smith : 2-6-04 thru 12-1-04
John smith : 8-1-04 thru null

Query for 2-1-04 thru 2-29-04 shows
John smith : 1-1-04 thru null
John smith : 2-6-04 thru 12-1-04

Sorry....got so deep into the thread that I'd forgotten the previous
starting point.

What is the NOAend field ? You want it to be Null in the query that returns
the max value? Or somewhere else?

While I wait for that clarification, if you want to filter the "max" date:

SELECT CarePlans.*
FROM CarePlans
WHERE CarePlans.SOCStart IN
(SELECT MAX(T.SOCStart)
FROM CarePlans AS T
WHERE T.ClientID=CarePlans.ClientID
--

Ken Snell
<MS ACCESS MVP>

AND T.SOCStart <= "#" &
Format([Enter the maximum date:], "mm/dd/yyyy") & "#");

OK...This appears to give the latest care plan for each client.
I am curious how it handles the null NOAEnd.

Let's try this approach first:

SELECT CarePlans.*
FROM CarePlans
WHERE CarePlans.SOCStart IN
(SELECT MAX(T.SOCStart)
FROM CarePlans AS T
WHERE T.ClientID=CarePlans.ClientID);


--

Ken Snell
<MS ACCESS MVP>


access 97


As asked elsethread, which ACCESS version are you using? The
suggestion
that
is in that other thread (using the [ ]. syntax) is something that
ACCESS
does automatically in some queries when you save and close them. Can
be
a
real problem to maintain, as once saved, ACCESS won't let you edit
them
easily. In those case, then I use a separate query that is the
subquery,
and
then join that query as a source table in the original query.

Post back with info and we'll see where we can go with this.

--

Ken Snell
<MS ACCESS MVP>


Syntax error is in the FROM clause

Looks ok to me...what syntax error does it give? and, assuming
that
you're
doing this in SQL view, where does the cursor "go" to show you
where
the
error is?

--

Ken Snell
<MS ACCESS MVP>

SELECT CarePlans.*
FROM CarePlans
INNER JOIN
(SELECT T.ClientID, MAX(T.SOCStart) as MostRecent
FROM CarePlans T
GROUP BY T.ClientID) as T1
ON CarePlans.ClientID = T1.ClientID
AND CarePlans.SOCStart = T1.MostRecent;


Post the actual SQL that is giving you the syntax error.

--

Ken Snell
<MS ACCESS MVP>

message
I am getting a syntax error in the qry_MostRecentCarePlan.
I
have
been
trying to figure it out but I am stumped on the

subquery
and

the
use
of
the
"T".


Jeff,

Does your CarePlans table contain an ID field?

Alright, Create a new query (call it
qry_MostRecentCarePlan)

SELECT CarePlans.*
FROM CarePlans
INNER JOIN
(SELECT T.ClientID, MAX(T.SOCStart) as MostRecent
FROM CarePlans T
GROUP BY T.ClientID) as T1
ON CarePlans.ClientID = T1.ClientID
AND CarePlans.SOCStart = T1.MostRecent

This query should give you a single record for each
client,
and
all
the
information will be from the most recent careplan

for
that

individual.
(assuming that SOCStart is the field that indicates the
most
recent
plan.

Now, replace references to the Careplan table in your
query
with
qry_MostRecentCarePlan, and you should have what you are
looking
for.

HTH
Dale

message
OK...The code I have sent is an example that I created
to
keep
things
simple
as the real code is much more complicated. You clearly
know
enough
to
solve
my problem although I am having trouble using the answer
you
have
given
and
relating back to the original code. Below is the
original
code.
This
shows
many records for each client. I just want the latest
(or
last)
for
each
client. Sorry about the confusion but I try not

to
put

too
much
into
the
message board as it might not be answered. Also, for
me,
this
is
hard
to
explain thoroughly. Any help is greatly

appreciated
and

if
you
choose
not
to spend any more time on this I will understand. Thanks
Jeff.



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))
OR
(((CarePlans.SOCStart)>[MonthStartDate] And
(CarePlans.SOCStart)<=[MonthEndDate]))
ORDER BY [ClientLastName] & ", " & [ClientFirstName];



"Ken Snell" <[email protected]>

wrote
in

message
T is an alias for the table Orders. You need to
differentiate
between
the
two copies of Orders table when you use a subquery.

I also note that I mistyped ClientID in the last
line...left
out
the
"t"
by
accident.

--

Ken Snell
<MS ACCESS MVP>

in
message
Thanks for the reply. I am trying to make

this
work

What
is
"T"
?

in
message
You can replace it with a subquery:

(SELECT Max(T.Orders) FROM
Orders AS T WHERE
T.ClientID = Orders.ClienID)

--

Ken Snell
<MS ACCESS MVP>

"jeff klein"

wrote
in
message
I am using DMax to show the last

"SOCStart"
for

all
customers.
This
function makes the query run very slow. Is
there
another
way
to
do
this
that might run faster? Below is the code
example.
Thank
you.



DMax("[SOCStart]","[Orders]","[Orders].[ClientID]
=
"
&
[Orders].[ClientID])

 

[Jeff Klein]

I am getting an error that indicates its too complex to be evaluated. If you
would like I could send table samples.

I have been working with a query that shows all records before
[MonthStartDate]
although I need to take it a step further and show only the last of each
client.


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

OK - let's try this:

SELECT CarePlans.*
FROM CarePlans
WHERE CarePlans.SOCStart IN
(SELECT MAX(T.SOCStart)
FROM CarePlans AS T
WHERE T.ClientID=CarePlans.ClientID
AND T.SOCStart <= "#" &
Format([Enter the maximum date:], "mm/dd/yyyy") & "#"
AND T.SOCStart <= "#" &
Format([Enter the maximum NOA date:], "mm/dd/yyyy") & "#");

I think this is headed in the direction you want to go?

--

Ken Snell
<MS ACCESS MVP>

Again..Thank you for the help. A little background. Our clients have NOA's
that hold information on services that they require. A client may have a
NOA that exists from 1-1-04(NOAStart) thru 4-15-04(NOAEnd) or sometimes the
NOAEnd is not entered (null) because the NOA is ongoing and the end is

to

be
determined at a later date.

The query you gave me works great and is much faster than the original

one

I
had that used DMax. Although my original "DMAX" query, and yours, only
returnes the latest NOAs. If I was returning NOA's for right now (on this
date) the one you gave me is the correct query. I am trying to improve the
query and return NOAs for particular dates. For example, if I needed the
NOAs that apply for 6-1-02 the "latest" type query would not be correct.

So, I need to return NOAs that are existing between a specified range
[MonthStart] and [MonthEnd], find the latest record within the specified
range, and also return any new records that come in during the range.

Example:
John smith : 1-1-02 thru 12-31-02
John smith : 1-1-03 thru null
John smith : 6-1-03 thru 12-31-03
John smith : 1-1-04 thru null
John smith : 2-6-04 thru 12-1-04
John smith : 8-1-04 thru null

Query for 2-1-04 thru 2-29-04 shows
John smith : 1-1-04 thru null
John smith : 2-6-04 thru 12-1-04

Sorry....got so deep into the thread that I'd forgotten the previous
starting point.

What is the NOAend field ? You want it to be Null in the query that returns
the max value? Or somewhere else?

While I wait for that clarification, if you want to filter the "max" date:

SELECT CarePlans.*
FROM CarePlans
WHERE CarePlans.SOCStart IN
(SELECT MAX(T.SOCStart)
FROM CarePlans AS T
WHERE T.ClientID=CarePlans.ClientID
--

Ken Snell
<MS ACCESS MVP>

AND T.SOCStart <= "#" &
Format([Enter the maximum date:], "mm/dd/yyyy") & "#");


OK...This appears to give the latest care plan for each client.
I am curious how it handles the null NOAEnd.

Let's try this approach first:

SELECT CarePlans.*
FROM CarePlans
WHERE CarePlans.SOCStart IN
(SELECT MAX(T.SOCStart)
FROM CarePlans AS T
WHERE T.ClientID=CarePlans.ClientID);


--

Ken Snell
<MS ACCESS MVP>


access 97


As asked elsethread, which ACCESS version are you using? The
suggestion
that
is in that other thread (using the [ ]. syntax) is something that
ACCESS
does automatically in some queries when you save and close

them.
Can

be
a
real problem to maintain, as once saved, ACCESS won't let you edit
them
easily. In those case, then I use a separate query that is the
subquery,
and
then join that query as a source table in the original query.

Post back with info and we'll see where we can go with this.

--

Ken Snell
<MS ACCESS MVP>


Syntax error is in the FROM clause

Looks ok to me...what syntax error does it give? and, assuming
that
you're
doing this in SQL view, where does the cursor "go" to show you
where
the
error is?

--

Ken Snell
<MS ACCESS MVP>

SELECT CarePlans.*
FROM CarePlans
INNER JOIN
(SELECT T.ClientID, MAX(T.SOCStart) as MostRecent
FROM CarePlans T
GROUP BY T.ClientID) as T1
ON CarePlans.ClientID = T1.ClientID
AND CarePlans.SOCStart = T1.MostRecent;


message
Post the actual SQL that is giving you the syntax error.

--

Ken Snell
<MS ACCESS MVP>

message
I am getting a syntax error in the qry_MostRecentCarePlan.
I
have
been
trying to figure it out but I am stumped on the subquery
and
the
use
of
the
"T".


Jeff,

Does your CarePlans table contain an ID field?

Alright, Create a new query (call it
qry_MostRecentCarePlan)

SELECT CarePlans.*
FROM CarePlans
INNER JOIN
(SELECT T.ClientID, MAX(T.SOCStart) as MostRecent
FROM CarePlans T
GROUP BY T.ClientID) as T1
ON CarePlans.ClientID = T1.ClientID
AND CarePlans.SOCStart = T1.MostRecent

This query should give you a single record for each
client,
and
all
the
information will be from the most recent careplan for
that
individual.
(assuming that SOCStart is the field that

indicates
the

most
recent
plan.

Now, replace references to the Careplan table in your
query
with
qry_MostRecentCarePlan, and you should have what

you
are

looking
for.

HTH
Dale

"Jeff Klein" <[email protected]>

wrote
in

message
OK...The code I have sent is an example that I created
to
keep
things
simple
as the real code is much more complicated. You
clearly
know
enough
to
solve
my problem although I am having trouble using the
answer
you
have
given
and
relating back to the original code. Below is the
original
code.
This
shows
many records for each client. I just want the latest
(or
last)
for
each
client. Sorry about the confusion but I try not to
put
too
much
into
the
message board as it might not be answered.

Also,
for

me,
this
is
hard
to
explain thoroughly. Any help is greatly appreciated
and
if
you
choose
not
to spend any more time on this I will understand.
Thanks
Jeff.



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))
OR
(((CarePlans.SOCStart)>[MonthStartDate] And
(CarePlans.SOCStart)<=[MonthEndDate]))
ORDER BY [ClientLastName] & ", " & [ClientFirstName];



in
message
T is an alias for the table Orders. You need to
differentiate
between
the
two copies of Orders table when you use a subquery.

I also note that I mistyped ClientID in the last
line...left
out
the
"t"
by
accident.

--

Ken Snell
<MS ACCESS MVP>

"jeff klein"

wrote
in
message
Thanks for the reply. I am trying to make this
work
What
is
"T"
?

"Ken Snell"

wrote
in
message
You can replace it with a subquery:

(SELECT Max(T.Orders) FROM
Orders AS T WHERE
T.ClientID = Orders.ClienID)

--

Ken Snell
<MS ACCESS MVP>

"jeff klein"

wrote
in
message
I am using DMax to show the last "SOCStart"
for
all
customers.
This
function makes the query run very slow. Is
there
another
way
to
do
this
that might run faster? Below is the code
example.
Thank
you.



DMax("[SOCStart]","[Orders]","[Orders].[ClientID]
=
"
&
[Orders].[ClientID])

 

[Ken Snell]

Zip up a small sample of the database with some records and email to me.
I'll look at it as time permits (I will be gone this weekend so it may be
early next week before I get back to you). Email is my reply address after
removing this is not real from it.

--

Ken Snell
<MS ACCESS MVP>

I am getting an error that indicates its too complex to be evaluated. If you
would like I could send table samples.

I have been working with a query that shows all records before
[MonthStartDate]
although I need to take it a step further and show only the last of each
client.


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

OK - let's try this:

SELECT CarePlans.*
FROM CarePlans
WHERE CarePlans.SOCStart IN
(SELECT MAX(T.SOCStart)
FROM CarePlans AS T
WHERE T.ClientID=CarePlans.ClientID
AND T.SOCStart <= "#" &
Format([Enter the maximum date:], "mm/dd/yyyy") & "#"
AND T.SOCStart <= "#" &
Format([Enter the maximum NOA date:], "mm/dd/yyyy") & "#");

I think this is headed in the direction you want to go?

--

Ken Snell
<MS ACCESS MVP>

Again..Thank you for the help. A little background. Our clients have NOA's
that hold information on services that they require. A client may

have

a

NOA that exists from 1-1-04(NOAStart) thru 4-15-04(NOAEnd) or

sometimes
the

NOAEnd is not entered (null) because the NOA is ongoing and the end is

to

be
determined at a later date.

The query you gave me works great and is much faster than the original

one

I
had that used DMax. Although my original "DMAX" query, and yours, only
returnes the latest NOAs. If I was returning NOA's for right now (on this
date) the one you gave me is the correct query. I am trying to

improve
the

query and return NOAs for particular dates. For example, if I needed the
NOAs that apply for 6-1-02 the "latest" type query would not be correct.

So, I need to return NOAs that are existing between a specified range
[MonthStart] and [MonthEnd], find the latest record within the specified
range, and also return any new records that come in during the range.

Example:
John smith : 1-1-02 thru 12-31-02
John smith : 1-1-03 thru null
John smith : 6-1-03 thru 12-31-03
John smith : 1-1-04 thru null
John smith : 2-6-04 thru 12-1-04
John smith : 8-1-04 thru null

Query for 2-1-04 thru 2-29-04 shows
John smith : 1-1-04 thru null
John smith : 2-6-04 thru 12-1-04



Sorry....got so deep into the thread that I'd forgotten the previous
starting point.

What is the NOAend field ? You want it to be Null in the query that
returns
the max value? Or somewhere else?

While I wait for that clarification, if you want to filter the "max" date:

SELECT CarePlans.*
FROM CarePlans
WHERE CarePlans.SOCStart IN
(SELECT MAX(T.SOCStart)
FROM CarePlans AS T
WHERE T.ClientID=CarePlans.ClientID
--

Ken Snell
<MS ACCESS MVP>

AND T.SOCStart <= "#" &
Format([Enter the maximum date:], "mm/dd/yyyy") & "#");


OK...This appears to give the latest care plan for each client.
I am curious how it handles the null NOAEnd.

Let's try this approach first:

SELECT CarePlans.*
FROM CarePlans
WHERE CarePlans.SOCStart IN
(SELECT MAX(T.SOCStart)
FROM CarePlans AS T
WHERE T.ClientID=CarePlans.ClientID);


--

Ken Snell
<MS ACCESS MVP>


access 97


As asked elsethread, which ACCESS version are you using? The
suggestion
that
is in that other thread (using the [ ]. syntax) is something that
ACCESS
does automatically in some queries when you save and close them.
Can
be
a
real problem to maintain, as once saved, ACCESS won't let

you
edit

them
easily. In those case, then I use a separate query that is the
subquery,
and
then join that query as a source table in the original query.

Post back with info and we'll see where we can go with this.

--

Ken Snell
<MS ACCESS MVP>


Syntax error is in the FROM clause

Looks ok to me...what syntax error does it give? and, assuming
that
you're
doing this in SQL view, where does the cursor "go" to

show
you

where
the
error is?

--

Ken Snell
<MS ACCESS MVP>

message
SELECT CarePlans.*
FROM CarePlans
INNER JOIN
(SELECT T.ClientID, MAX(T.SOCStart) as MostRecent
FROM CarePlans T
GROUP BY T.ClientID) as T1
ON CarePlans.ClientID = T1.ClientID
AND CarePlans.SOCStart = T1.MostRecent;


message
Post the actual SQL that is giving you the syntax error.

--

Ken Snell
<MS ACCESS MVP>

message
I am getting a syntax error in the
qry_MostRecentCarePlan.
I
have
been
trying to figure it out but I am stumped on the subquery
and
the
use
of
the
"T".


Jeff,

Does your CarePlans table contain an ID field?

Alright, Create a new query (call it
qry_MostRecentCarePlan)

SELECT CarePlans.*
FROM CarePlans
INNER JOIN
(SELECT T.ClientID, MAX(T.SOCStart) as MostRecent
FROM CarePlans T
GROUP BY T.ClientID) as T1
ON CarePlans.ClientID = T1.ClientID
AND CarePlans.SOCStart = T1.MostRecent

This query should give you a single record for each
client,
and
all
the
information will be from the most recent

careplan
for

that
individual.
(assuming that SOCStart is the field that indicates
the
most
recent
plan.

Now, replace references to the Careplan table in your
query
with
qry_MostRecentCarePlan, and you should have what you
are
looking
for.

HTH
Dale

in
message
OK...The code I have sent is an example that I
created
to
keep
things
simple
as the real code is much more complicated. You
clearly
know
enough
to
solve
my problem although I am having trouble using the
answer
you
have
given
and
relating back to the original code. Below is the
original
code.
This
shows
many records for each client. I just want the
latest
(or
last)
for
each
client. Sorry about the confusion but I try

not
to

put
too
much
into
the
message board as it might not be answered. Also,
for
me,
this
is
hard
to
explain thoroughly. Any help is greatly appreciated
and
if
you
choose
not
to spend any more time on this I will understand.
Thanks
Jeff.



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))
OR
(((CarePlans.SOCStart)>[MonthStartDate] And
(CarePlans.SOCStart)<=[MonthEndDate]))
ORDER BY [ClientLastName] & ", " &
[ClientFirstName];



in
message
T is an alias for the table Orders. You need to
differentiate
between
the
two copies of Orders table when you use a
subquery.

I also note that I mistyped ClientID in the last
line...left
out
the
"t"
by
accident.

--

Ken Snell
<MS ACCESS MVP>

"jeff klein"

wrote
in
message
Thanks for the reply. I am trying to make this
work
What
is
"T"
?

"Ken Snell"

wrote
in
message
You can replace it with a subquery:

(SELECT Max(T.Orders) FROM
Orders AS T WHERE
T.ClientID = Orders.ClienID)

--

Ken Snell
<MS ACCESS MVP>

"jeff klein"
<[email protected]>
wrote
in
message
I am using DMax to show the last "SOCStart"
for
all
customers.
This
function makes the query run very

slow.
Is

there
another
way
to
do
this
that might run faster? Below is the code
example.
Thank
you.



DMax("[SOCStart]","[Orders]","[Orders].[ClientID]
=
"
&
[Orders].[ClientID])