DMax alternative?

J

jeff klein

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])
 
K

Ken Snell

You can replace it with a subquery:

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

jeff klein

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

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

Ken Snell

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 said:
Thanks for the reply. I am trying to make this work What is "T" ?

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

Jeff Klein

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 said:
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 said:
Thanks for the reply. I am trying to make this work What is "T" ?

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

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...
 
D

Dale Fye

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 said:
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 said:
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 said:
Thanks for the reply. I am trying to make this work What is "T" ?

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>

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
Click to expand...
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...
 
G

Gary Walter

Hi Jeff and Dale,

PMFBI

In the "speed-me-up" world,
I believe this is a good method,
but I get the idea we don't want
to *limit* CarePlans to "most recent."

I might suggest removing

"AND CarePlans.SOCStart = T1.MostRecent"

so preliminary query might be better named like

"qryCarePlanWithMostRecent"

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;

so your final query might be:

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

or just include in original as subquery (not necessarily "better" in my view)

PARAMETERS [MonthStartDate] DateTime, [MonthEndDate] DateTime;
SELECT
[ClientLastName] & ", " & [ClientFirstName] AS ClientLastandFirst,
CarePlans.SOCStart,
CarePlans.NOAEnd,
nz([noaend],#12/31/9999#) AS ActualNOAEnd,
T1.MostRecent
FROM
(Clients
INNER JOIN
CarePlans
ON Clients.ClientID = CarePlans.ClientID)
INNER JOIN
(SELECT T.ClientID, MAX(T.SOCStart) as MostRecent
FROM CarePlans T
GROUP BY T.ClientID) as T1
ON CarePlans.ClientID = T1.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];

Apologies again for butting in.

Gary Walter

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

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

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>

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...
 
G

Gary Walter

sorry..forgot to include *most recent*...


"qryCarePlanWithMostRecent"

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

so your final query might be:

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

Gary Walter

One more idea, and probably the
"most understandable"...

make the following separate query:

qryMostRecent

SELECT
ClientID,
MAX(SOCStart) as MostRecent
FROM CarePlans
GROUP BY ClientID;

then inner join this query in original query


PARAMETERS [MonthStartDate] DateTime, [MonthEndDate] DateTime;
SELECT
[ClientLastName] & ", " & [ClientFirstName] AS ClientLastandFirst,
CarePlans.SOCStart,
CarePlans.NOAEnd,
nz([noaend],#12/31/9999#) AS ActualNOAEnd,
qryMostRecent.MostRecent
FROM
(Clients INNER JOIN CarePlans
ON Clients.ClientID = CarePlans.ClientID)
INNER JOIN qryMostRecent
ON qryMostRecent.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];

So save the first query, then in your original query
(in query designer), add the query, join the ClientID's,
and drag-and-drop "MostRecent" down to a Field row.

Please respond back if I have not been clear about something
(or if this still is "slow").

Gary Walter
 
K

Ken Snell

Looks like Gary and Dale have suggested some solutions. Post back if you
need more assistance.
 
J

Jeff Klein

Gary, Thank you for your help. I have tried your code and I still get many
records per customer. When I run the qryMostRecent by itself it seems to
show the most recent records per customer. I understand the query and it
looks like it should work!
 
G

Gary Walter

Hi Jeff,

Looking back I realize now that Dale's
solution *was* what you wanted
and apologise for butting in.

Did you try his solution?

Gary Walter

Gary, Thank you for your help. I have tried your code and I still get many
records per customer. When I run the qryMostRecent by itself it seems to
show the most recent records per customer. I understand the query and it
looks like it should work!



Gary Walter said:
One more idea, and probably the
"most understandable"...

make the following separate query:

qryMostRecent

SELECT
ClientID,
MAX(SOCStart) as MostRecent
FROM CarePlans
GROUP BY ClientID;

then inner join this query in original query


PARAMETERS [MonthStartDate] DateTime, [MonthEndDate] DateTime;
SELECT
[ClientLastName] & ", " & [ClientFirstName] AS ClientLastandFirst,
CarePlans.SOCStart,
CarePlans.NOAEnd,
nz([noaend],#12/31/9999#) AS ActualNOAEnd,
qryMostRecent.MostRecent
FROM
(Clients INNER JOIN CarePlans
ON Clients.ClientID = CarePlans.ClientID)
INNER JOIN qryMostRecent
ON qryMostRecent.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];

So save the first query, then in your original query
(in query designer), add the query, join the ClientID's,
and drag-and-drop "MostRecent" down to a Field row.

Please respond back if I have not been clear about something
(or if this still is "slow").

Gary Walter
Click to expand...
Click to expand...
 
J

Jeff Klein

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".


Dale Fye said:
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 said:
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 said:
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
Click to expand...
"t"
by
accident.

--

Ken Snell
<MS ACCESS MVP>

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

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>

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...
 
K

Ken Snell

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

--

Ken Snell
<MS ACCESS MVP>

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


Dale Fye said:
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 said:
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
Click to expand...
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
Click to expand...
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>

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

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>

I am using DMax to show the last "SOCStart" for all customers.
This
function makes the query run very slow. Is there another way
Click to expand...
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...
 
J

Jeff Klein

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;


Ken Snell said:
Post the actual SQL that is giving you the syntax error.

--

Ken Snell
<MS ACCESS MVP>

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


Dale Fye said:
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
Click to expand...
into
the
message board as it might not be answered. Also, for me, this is
Click to expand...
hard
to
explain thoroughly. Any help is greatly appreciated and if you
Click to expand...
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
Click to expand...
the
"t"
by
accident.

--

Ken Snell
<MS ACCESS MVP>

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

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>

I am using DMax to show the last "SOCStart" for all customers.
This
function makes the query run very slow. Is there another
Click to expand...
Click to expand...
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...
 
K

Ken Snell

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>

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


Ken Snell said:
Post the actual SQL that is giving you the syntax error.

--

Ken Snell
<MS ACCESS MVP>

of
the
Click to expand...
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)
Click to expand...
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>

Thanks for the reply. I am trying to make this work What is
Click to expand...
Click to expand...
"T"
?
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>

I am using DMax to show the last "SOCStart" for all customers.
This
function makes the query run very slow. Is there another
Click to expand...
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...
 
J

Jeff Klein

Syntax error is in the FROM clause

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

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


Ken Snell said:
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
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
out
the
"t"
by
accident.

--

Ken Snell
<MS ACCESS MVP>

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

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>

I am using DMax to show the last "SOCStart" for all
customers.
This
function makes the query run very slow. Is there
Click to expand...
another
way
to
do
this
that might run faster? Below is the code example.
Click to expand...
Thank
you.
DMax("[SOCStart]","[Orders]","[Orders].[ClientID] = " &
[Orders].[ClientID])
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Jeff,

What version of Access are you using? In Access 97, you
might have to write it like:

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;

Note that I replaced the () that were the boundries of the
subquery with brackets[] and inserted a period after the
second bracket.

HTH
Dale
-----Original Message-----
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>

qry_MostRecentCarePlan. I have
been subquery and the use
of
Click to expand...
each client, and
all
careplan for that
individual.
Click to expand...
indicates the most
recent
what you are looking
for. You clearly know
enough
Click to expand...
the answer you
have
the original code.
This the latest (or last)
for
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] & ", " & [ClientFirstName] AS
ClientLastandFirst,
CarePlans.SOCStart, CarePlans.NOAEnd, nz
Click to expand...
Click to expand...
([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"
Click to expand...
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
Click to expand...
Click to expand...
last line...left
out
message make this work What is
"T"
Click to expand...
slow. Is there
another code example.
Thank
you.


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


.
Click to expand...
 
K

Ken Snell

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

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

Jeff Klein said:
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
Click to expand...
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
Click to expand...
code.
This
shows
many records for each client. I just want the latest (or
Click to expand...
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,
Click to expand...
Click to expand...
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>

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

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>

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...
 
J

Jeff Klein

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

Ken Snell said:
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,
Click to expand...
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,
Click to expand...
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,
Click to expand...
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
Click to expand...
line...left
out
the
"t"
by
accident.

--

Ken Snell
<MS ACCESS MVP>

Thanks for the reply. I am trying to make this work
Click to expand...
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>

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

DMAX function in query 1
Record Number in Query 2
Really slow query 2
MS ACCESS Jet SQL: Update statements that utilize saved queries/dmax 3
Insert only unique records to new table 2
Query questions 1
Updateting WO Number 2
Assigning sequential numbers 9

Top