Exclude most recent record numbers

[Linda RQ]

Hi Everyone,

Using Access 2003. I have a query and I want to put criteria in my the ID
field to not show the record numbers that are the most recent. For instance
if my highest ID number is 450 I only want to show any records with an ID
number 400 and below. As new records get added, my ID numbers will be
higher so let's say tomorrow the highest ID number is 600, I want to only
show records 550 and below.

Thanks,
Linda

 

[Michel Walsh]

SELECT *
FROM somewhere
WHERE id < (SELECT MAX(id) FROM somewhere)



should do.


If you add a record, from this query, and if the new record becomes the new
max id, the previously hidden record won't automatically re-appear, neither
the new record disappears. You will have to requery the form, or to close
and re-open the query (in data view).




Vanderghast, Access MVP

 

[fredg]

Hi Everyone,

Using Access 2003. I have a query and I want to put criteria in my the ID
field to not show the record numbers that are the most recent. For instance
if my highest ID number is 450 I only want to show any records with an ID
number 400 and below. As new records get added, my ID numbers will be
higher so let's say tomorrow the highest ID number is 600, I want to only
show records 550 and below.

Thanks,
Linda

How is Access supposed to know how many records you have added?
1? 23? 259?

I would suggest you add a [DataEntered] Date datatype field to your
underlying table. Set it's DefaultValue property to
=Date().

Include this field on your data entry form.

Then, after you have entered new records, each record will have it's
entry date.
Now you can simply include the [DateEntered] field in your query. As
criteria on this column, write:
<DMax("[DateEntered]","TableName]")

All records, except for the last [DateEntered], will appear in the
query.

 

[Conan Kelly]

Michel,

Please forgive me if I'm incorrect here, but from the sounds of OP, they
want all records except the 50 newest records. So your suggestion modified
slightly should do the trick:

SELECT *
FROM somewhere
WHERE id < ((SELECT MAX(id) FROM somewhere) -50)

HTH,

Conan

 

[Michel Walsh]

Exactly. It could be interesting to have the criteria based on the actual
date and time against the date_time stamp saved in the record.

Vanderghast, Access MVP