Selecting only records older than a date ?

  • Thread starter Thread starter TonyB
  • Start date Start date
T

TonyB

Hi,
I have a table which has several records. In each record there is a date
field and a name field (among others). I want to select only records where
the most recent name and date combinations are older than say 1 month ? I
can see how to select any record that is older than 1 month, but I only want
records that are older than 1 month and that do not have a more recent name
field.
for example
Name Date
Bill 1Feb2004
Bill 1 May 2004
Ben 1Mar 2004
Ben 1 Apr 2004
Fred 1 Mar 2004
the query would select only select records Ben 1 Apr 2004 and Fred 1 Mar
2004, but Bill would not be selected since there is a record with Bill with
date within the last month although there are records with Bill that are
older than 1 month.
Regards
Tony
 
Try something along the lines of the following untested aircode

SELECT ................
FROM YourTable T2
WHERE [name] NOT IN (SELECT DISTINCT [name] FROM YourTable
T2 WHERE T2.[date] > DateAdd ("m", -1 , date))
AND [date] IN (SELECT Max([Date]) FROM YourTable T3 WHERE
T3.[name] = T1.[name])

Hope This Helps
Gerald Stanley MCSD
 
Hi Gerald,
Thanks for replying. I'm a newcomer to SQL. I sort of understand that you
are selecting records that have the latest date for each value of name, and
those that are less than one month of old, and then selecting only those
records that do not satisfy those requirements ?
What does your T2 T1 T3 terminology mean ? Say my table is tblMain how does
your example read then ?
Regards
Tony

Gerald Stanley said:
Try something along the lines of the following untested aircode

SELECT ................
FROM YourTable T2
WHERE [name] NOT IN (SELECT DISTINCT [name] FROM YourTable
T2 WHERE T2.[date] > DateAdd ("m", -1 , date))
AND [date] IN (SELECT Max([Date]) FROM YourTable T3 WHERE
T3.[name] = T1.[name])

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Hi,
I have a table which has several records. In each record there is a date
field and a name field (among others). I want to select only records where
the most recent name and date combinations are older than say 1 month ? I
can see how to select any record that is older than 1 month, but I only want
records that are older than 1 month and that do not have a more recent name
field.
for example
Name Date
Bill 1Feb2004
Bill 1 May 2004
Ben 1Mar 2004
Ben 1 Apr 2004
Fred 1 Mar 2004
the query would select only select records Ben 1 Apr 2004 and Fred 1 Mar
2004, but Bill would not be selected since there is a record with Bill with
date within the last month although there are records with Bill that are
older than 1 month.
Regards
Tony


.
Click to expand...
Click to expand...
 
You just need to substitute YourTable with tblMain. T1, T2
and T3 are table aliases. The full synatx is FROM
YourTable AS T2 but the AS is not mandatory. They are
neccesary in this piece of SQL as the same table is used
more than once and these unique qualifiers make explicit
where the various references are coming from. I actually
use them in all but the simplest SQL statements as I find
they add clarity and are less time-consuming than typing
full table names.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Hi Gerald,
Thanks for replying. I'm a newcomer to SQL. I sort of understand that you
are selecting records that have the latest date for each value of name, and
those that are less than one month of old, and then selecting only those
records that do not satisfy those requirements ?
What does your T2 T1 T3 terminology mean ? Say my table is tblMain how does
your example read then ?
Regards
Tony

Try something along the lines of the following untested aircode

SELECT ................
FROM YourTable T2
WHERE [name] NOT IN (SELECT DISTINCT [name] FROM YourTable
T2 WHERE T2.[date] > DateAdd ("m", -1 , date))
AND [date] IN (SELECT Max([Date]) FROM YourTable T3 WHERE
T3.[name] = T1.[name])

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Hi,
I have a table which has several records. In each record there is a date
field and a name field (among others). I want to select only records where
the most recent name and date combinations are older than say 1 month ? I
can see how to select any record that is older than 1 month, but I only want
records that are older than 1 month and that do not have a more recent name
field.
for example
Name Date
Bill 1Feb2004
Bill 1 May 2004
Ben 1Mar 2004
Ben 1 Apr 2004
Fred 1 Mar 2004
the query would select only select records Ben 1 Apr 2004 and Fred 1 Mar
2004, but Bill would not be selected since there is a record with Bill with
date within the last month although there are records with Bill that are
older than 1 month.
Regards
Tony


.
Click to expand...
Click to expand...


.
Click to expand...
 
Gerald,
Thanks for your patience. Looking at your code T1 is not declared as an
alias ?

SELECT ................
FROM tblMain T2
WHERE [name] NOT IN (SELECT DISTINCT [name] FROM tblMain
T2 WHERE T2.[date] > DateAdd ("m", -1 , date))
AND [date] IN (SELECT Max([Date]) FROM tblMain T3 WHERE T3.[name] =
T1.[name])

Should the first FROM tblMain Table be T1 not T2 ?
Regards
Tony

Gerald Stanley said:
You just need to substitute YourTable with tblMain. T1, T2
and T3 are table aliases. The full synatx is FROM
YourTable AS T2 but the AS is not mandatory. They are
neccesary in this piece of SQL as the same table is used
more than once and these unique qualifiers make explicit
where the various references are coming from. I actually
use them in all but the simplest SQL statements as I find
they add clarity and are less time-consuming than typing
full table names.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Hi Gerald,
Thanks for replying. I'm a newcomer to SQL. I sort of understand that you
are selecting records that have the latest date for each value of name, and
those that are less than one month of old, and then selecting only those
records that do not satisfy those requirements ?
What does your T2 T1 T3 terminology mean ? Say my table is tblMain how does
your example read then ?
Regards
Tony

Try something along the lines of the following untested aircode

SELECT ................
FROM YourTable T2
WHERE [name] NOT IN (SELECT DISTINCT [name] FROM YourTable
T2 WHERE T2.[date] > DateAdd ("m", -1 , date))
AND [date] IN (SELECT Max([Date]) FROM YourTable T3 WHERE
T3.[name] = T1.[name])

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Hi,
I have a table which has several records. In each record
there is a date
field and a name field (among others). I want to select
only records where
the most recent name and date combinations are older than
say 1 month ? I
can see how to select any record that is older than 1
month, but I only want
records that are older than 1 month and that do not have a
more recent name
field.
for example
Name Date
Bill 1Feb2004
Bill 1 May 2004
Ben 1Mar 2004
Ben 1 Apr 2004
Fred 1 Mar 2004
the query would select only select records Ben 1 Apr 2004
and Fred 1 Mar
2004, but Bill would not be selected since there is a
record with Bill with
date within the last month although there are records with
Bill that are
older than 1 month.
Regards
Tony
Click to expand...
Click to expand...
Click to expand...
 
Tony

Good spot and correct analysis. The first instance of
tblMain should be aliased as T1.

Gerald Stanley MCSD
-----Original Message-----
Gerald,
Thanks for your patience. Looking at your code T1 is not declared as an
alias ?

SELECT ................
FROM tblMain T2
WHERE [name] NOT IN (SELECT DISTINCT [name] FROM tblMain
T2 WHERE T2.[date] > DateAdd ("m", -1 , date))
AND [date] IN (SELECT Max([Date]) FROM tblMain T3 WHERE T3.[name] =
T1.[name])

Should the first FROM tblMain Table be T1 not T2 ?
Regards
Tony

You just need to substitute YourTable with tblMain. T1, T2
and T3 are table aliases. The full synatx is FROM
YourTable AS T2 but the AS is not mandatory. They are
neccesary in this piece of SQL as the same table is used
more than once and these unique qualifiers make explicit
where the various references are coming from. I actually
use them in all but the simplest SQL statements as I find
they add clarity and are less time-consuming than typing
full table names.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Hi Gerald,
Thanks for replying. I'm a newcomer to SQL. I sort of understand that you
are selecting records that have the latest date for each value of name, and
those that are less than one month of old, and then selecting only those
records that do not satisfy those requirements ?
What does your T2 T1 T3 terminology mean ? Say my table is tblMain how does
your example read then ?
Regards
Tony

Try something along the lines of the following untested aircode

SELECT ................
FROM YourTable T2
WHERE [name] NOT IN (SELECT DISTINCT [name] FROM YourTable
T2 WHERE T2.[date] > DateAdd ("m", -1 , date))
AND [date] IN (SELECT Max([Date]) FROM YourTable T3 WHERE
T3.[name] = T1.[name])

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Hi,
I have a table which has several records. In each record
there is a date
field and a name field (among others). I want to select
only records where
the most recent name and date combinations are older than
say 1 month ? I
can see how to select any record that is older than 1
month, but I only want
records that are older than 1 month and that do not have a
more recent name
field.
for example
Name Date
Bill 1Feb2004
Bill 1 May 2004
Ben 1Mar 2004
Ben 1 Apr 2004
Fred 1 Mar 2004
the query would select only select records Ben 1 Apr 2004
and Fred 1 Mar
2004, but Bill would not be selected since there is a
record with Bill with
date within the last month although there are records with
Bill that are
older than 1 month.
Regards
Tony
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top