Finding Previous Date(s)

[mcgj]

I am looking for the best way to find the previous date of treatment from any
date.

I have a table that hold dates representing the last time a treatment was
made to a particular entity. These entities are related by a super-grouping
(several even though I only show one below). When treatments are made an
entity may or may not be treated within a super-grouping.

SupGrp EntitiyId Date
1 A 9/10/2007
1 B 9/10/2007
1 C 9/10/2007
1 B 9/15/2007
1 A 10/10/2007

So let's say I want the last application data previous to 10/15/2007 for
each entity within super-group 1. I would want the result to look something
like this:

SupGrp EntitiyId Date
1 A 10/10/2007
1 B 9/15/2007
1 C 9/10/2007

What is the best way to do this with a query?

 

[Jeanette Cunningham]

Hi,
You need to start with a query that returns all dates before 10/15/2007 for
each entity.
Use this query to find the most recent date for each entity using Max in the
query.
If you haven't used Max before, use the help.

Jeanette Cunningham

 

[KARL DEWEY]

Try this ---
SELECT mcgj.SupGrp, mcgj.EntitiyId, Max(mcgj.TreatDate) AS MaxOfTreatDate
FROM mcgj
WHERE (((mcgj.TreatDate)<=[Enter cutoff date]))
GROUP BY mcgj.SupGrp, mcgj.EntitiyId;

 

[Michael Gramelspacher]

I am looking for the best way to find the previous date of treatment from any
date.

I have a table that hold dates representing the last time a treatment was
made to a particular entity. These entities are related by a super-grouping
(several even though I only show one below). When treatments are made an
entity may or may not be treated within a super-grouping.

SupGrp EntitiyId Date
1 A 9/10/2007
1 B 9/10/2007
1 C 9/10/2007
1 B 9/15/2007
1 A 10/10/2007

So let's say I want the last application data previous to 10/15/2007 for
each entity within super-group 1. I would want the result to look something
like this:

SupGrp EntitiyId Date
1 A 10/10/2007
1 B 9/15/2007
1 C 9/10/2007

What is the best way to do this with a query?

try:

SELECT SupGp,
EntityID,
MAX([Date]) AS [Previous Date]
FROM (SELECT t1.SupGp,
t1.EntityID,
t1.DATE
FROM Treatments AS t1
WHERE t1.[Date] <#10/15/2007#) AS t
GROUP BY SupGp, EntityID;

 

[mcgj]

Jeanette -- This is the way I eventually made it work. I like the idea of
not having to have two queries. The ones that were posted after yours
concatenated all the searching into one query. Thank you very much for you
help though!!

mc

 

[mcgj]

Karl -- This worked very well! But when I got back to the point when there
were no previous applications the query returned nothing. I know I did not
post anything about this in my previous posting but is there a way to still
get it to return a list of EntityIds for a particular SupGrp? I would like
it to list "No Previous App". I am thinking there may be a way to test for
NULL but I am not sure. Anyway thanks for you post it was perfect for
getting what I ask for.

mc

Try this ---
SELECT mcgj.SupGrp, mcgj.EntitiyId, Max(mcgj.TreatDate) AS MaxOfTreatDate
FROM mcgj
WHERE (((mcgj.TreatDate)<=[Enter cutoff date]))
GROUP BY mcgj.SupGrp, mcgj.EntitiyId;

--
KARL DEWEY
Build a little - Test a little

I am looking for the best way to find the previous date of treatment from any
date.

I have a table that hold dates representing the last time a treatment was
made to a particular entity. These entities are related by a super-grouping
(several even though I only show one below). When treatments are made an
entity may or may not be treated within a super-grouping.

SupGrp EntitiyId Date
1 A 9/10/2007
1 B 9/10/2007
1 C 9/10/2007
1 B 9/15/2007
1 A 10/10/2007

So let's say I want the last application data previous to 10/15/2007 for
each entity within super-group 1. I would want the result to look something
like this:

SupGrp EntitiyId Date
1 A 10/10/2007
1 B 9/15/2007
1 C 9/10/2007

What is the best way to do this with a query?

 

[mcgj]

Michael -- This worked too! I think I am going to need a book or something
on SQL as the more I get into working with DBs the more critical it becomes.
Do you know if there is a way to achieve this:
When I got to the point when there were no previous applications the query
returned nothing. I know I did not post anything about this in my previous
posting but is there a way to still get it to return a list of EntityIds for
a particular SupGrp? I would like it to list "No Previous App". I am
thinking there may be a way to test for NULL but I am not sure. Anyway
thanks for you post it was perfect for getting what I ask for.

mc

PS does anyone have a recommendation on the best way to learn Access SQL? I
have Office 2003. And does anyone else find the small font of the SQL editor
annoying???

Thanks again.

I am looking for the best way to find the previous date of treatment from any
date.

I have a table that hold dates representing the last time a treatment was
made to a particular entity. These entities are related by a super-grouping
(several even though I only show one below). When treatments are made an
entity may or may not be treated within a super-grouping.

SupGrp EntitiyId Date
1 A 9/10/2007
1 B 9/10/2007
1 C 9/10/2007
1 B 9/15/2007
1 A 10/10/2007

So let's say I want the last application data previous to 10/15/2007 for
each entity within super-group 1. I would want the result to look something
like this:

SupGrp EntitiyId Date
1 A 10/10/2007
1 B 9/15/2007
1 C 9/10/2007

What is the best way to do this with a query?

try:

SELECT SupGp,
EntityID,
MAX([Date]) AS [Previous Date]
FROM (SELECT t1.SupGp,
t1.EntityID,
t1.DATE
FROM Treatments AS t1
WHERE t1.[Date] <#10/15/2007#) AS t
GROUP BY SupGp, EntityID;

 

[Michael Gramelspacher]

When I got to the point when there were no previous applications the query
returned nothing. I know I did not post anything about this in my previous
posting but is there a way to still get it to return a list of EntityIds for
a particular SupGrp? I would like it to list "No Previous App". I am
thinking there may be a way to test for NULL but I am not sure.

SELECT Treatments.SupGp,
Treatments.EntityID,
IIF(a.[Previous Date] IS NULL,"No Previous App",
a.[Previous Date]) AS [Last Appt]
FROM Treatments
LEFT JOIN (SELECT SupGp,
EntityID,
MAX([Date]) AS [Previous Date]
FROM (SELECT t1.SupGp,
t1.EntityID,
t1.DATE
FROM Treatments AS t1
WHERE t1.DATE <# 10 / 15 / 2007 #) AS t
GROUP BY SupGp,EntityID) AS a
ON (Treatments.EntityID = a.EntityID)
AND (Treatments.SupGp = a.SupGp)
GROUP BY Treatments.SupGp,Treatments.EntityID,a.[Previous Date];