DMax alternative?

  • Thread starter Thread starter jeff klein
  • Start date Start date
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>


Jeff Klein said:
access 97


Ken Snell said:
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>


Jeff Klein said:
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
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
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]
Click to expand...
Click to expand...
=
"
&
[Orders].[ClientID])
Click to expand...
Click to expand...
Click to expand...
Click to expand...
Click to expand...
 
OK...This appears to give the latest care plan for each client.
I am curious how it handles the null NOAEnd.

Ken Snell said:
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>


Jeff Klein said:
access 97


Ken Snell said:
As asked elsethread, which ACCESS version are you using? The
Click to expand...
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
Click to expand...
Click to expand...
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
Click to expand...
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
Click to expand...
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.
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
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.
Click to expand...
Click to expand...
DMax("[SOCStart]","[Orders]","[Orders].[ClientID]
=
"
&
[Orders].[ClientID])
Click to expand...
Click to expand...
Click to expand...
Click to expand...
 
The problem I see with the latest record is that I might need to show
records for a month that has past.

Ken Snell said:
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>


Jeff Klein said:
access 97


Ken Snell said:
As asked elsethread, which ACCESS version are you using? The
Click to expand...
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
Click to expand...
Click to expand...
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
Click to expand...
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
Click to expand...
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.
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
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.
Click to expand...
Click to expand...
DMax("[SOCStart]","[Orders]","[Orders].[ClientID]
=
"
&
[Orders].[ClientID])
Click to expand...
Click to expand...
Click to expand...
Click to expand...
 
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") & "#");


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

Ken Snell said:
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>


Jeff Klein said:
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
Click to expand...
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
Click to expand...
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
Click to expand...
clearly
know
enough
to
solve
my problem although I am having trouble using the
Click to expand...
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
Click to expand...
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
Click to expand...
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]>
Click to expand...
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"
Click to expand...
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.
Click to expand...
DMax("[SOCStart]","[Orders]","[Orders].[ClientID]
=
"
&
[Orders].[ClientID])
Click to expand...
Click to expand...
Click to expand...
Click to expand...
Click to expand...
 
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



Ken Snell said:
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") & "#");


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

Ken Snell said:
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.
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
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,
Click to expand...
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
Click to expand...
Click to expand...
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"
Click to expand...
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.
Click to expand...
DMax("[SOCStart]","[Orders]","[Orders].[ClientID]
=
"
&
[Orders].[ClientID])
Click to expand...
Click to expand...
Click to expand...
Click to expand...
 
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>

Jeff Klein said:
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



Ken Snell said:
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") & "#");


Jeff Klein said:
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
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
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] & ", " &
Click to expand...
Click to expand...
[ClientFirstName]
AS
ClientLastandFirst,
CarePlans.SOCStart, CarePlans.NOAEnd,
nz([noaend],#12/31/9999#)
AS
ActualNOAEnd
FROM Clients INNER JOIN CarePlans ON
Click to expand...
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]>
Click to expand...
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
Click to expand...
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"
Click to expand...
wrote
in
message
I am using DMax to show the last
Click to expand...
"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])
Click to expand...
Click to expand...
Click to expand...
 
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;



Ken Snell said:
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>

Jeff Klein said:
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
Click to expand...
to
be
determined at a later date.

The query you gave me works great and is much faster than the original
Click to expand...
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



Ken Snell said:
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
Click to expand...
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
Click to expand...
indicates
the
most
recent
plan.

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

HTH
Dale

"Jeff Klein" <[email protected]>
Click to expand...
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.
Click to expand...
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] & ", " &
Click to expand...
[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"
Click to expand...
wrote
in
message
Thanks for the reply. I am trying to make this
work
What
is
"T"
?

"Ken Snell"
Click to expand...
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"
Click to expand...
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])
Click to expand...
Click to expand...
Click to expand...
 
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>

Jeff Klein said:
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;



Ken Snell said:
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>

Jeff Klein said:
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
Click to expand...
Click to expand...
have
a
NOA that exists from 1-1-04(NOAStart) thru 4-15-04(NOAEnd) or
Click to expand...
sometimes
the
NOAEnd is not entered (null) because the NOA is ongoing and the end is
Click to expand...
to
be
determined at a later date.

The query you gave me works great and is much faster than the original
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
(((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"
Click to expand...
wrote
in
message
Thanks for the reply. I am trying to make this
work
What
is
"T"
?

"Ken Snell"
Click to expand...
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
Click to expand...
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])
Click to expand...
Click to expand...
Click to expand...
 
Back
Top