Show record closets to today’s date.

[MFranz]

Hi Everyone,

I currently have a table that records a property’s most recent Title V
inspection status (pass, fail, needs repair) and the inspection date. Each
property can have multiple records covering different dates. What I’m trying
to do is create a query that will only show me the most recent record as of
the computers current date Date() for those properties with a fail status.
Any help would be greatly appreciated. Thanks in advance.

Matt Franz

 

[Sharkbyte]

Not quite sure how you could return a single record, without being even more
specific. However, if you query the table in question, asking for all
records > date() with a Status of "Fail", and sort descending. You can
format the form so you only see the first record returned, which should also
be the most recent. Just hide the navigation buttons, and your users can't
get to the next record.

HTH

Sharkbyte

 

[MFranz]

Hi Sharkbyte,

Thanks for the response. Here is a bit more detail. I have a table that
shows inspection date (mm/dd/yyyy) and inspection result (pass, fail, repair)
for each parcel (each parcel has it’s own id number) within the community. A
parcel may have more than one record associated with it to represent each of
the different inspections done.
What I would like to do is create a query that will show me the newest
record (the record whose inspection date is closest to the current date
(Date()) for each parcel.
So if I have 3 parcels (p1,p2,p3) with 3 records each (for a total of 9
records) and I looked at it today (2/8/06), the query would only show me a
total of 3 records (one record for each parcel that is closest to today’s
date). Thanks again for the help.

Matt Franz

 

[schasteen]

I am unsure if you want parcels with today's date or just the most recent
date. For the first situation place Date() in the criteria of your query.
For the second have your queary:
Select max([parcel date]),....
from yourtable
Group by "all the other fields"
or hit the sigma symbol on the design view of your query and in the total
row select max for the date field

 

[MFranz]

Hi Schasteen,

A big thanks for your help, your second suggestion was exactly what I was
looking for. It’s amazing how something so simple can be so difficult to
figure out. Thanks again for the help.

MFranz

I am unsure if you want parcels with today's date or just the most recent
date. For the first situation place Date() in the criteria of your query.
For the second have your queary:
Select max([parcel date]),....
from yourtable
Group by "all the other fields"
or hit the sigma symbol on the design view of your query and in the total
row select max for the date field

Hi Sharkbyte,

Thanks for the response. Here is a bit more detail. I have a table that
shows inspection date (mm/dd/yyyy) and inspection result (pass, fail, repair)
for each parcel (each parcel has it’s own id number) within the community. A
parcel may have more than one record associated with it to represent each of
the different inspections done.
What I would like to do is create a query that will show me the newest
record (the record whose inspection date is closest to the current date
(Date()) for each parcel.
So if I have 3 parcels (p1,p2,p3) with 3 records each (for a total of 9
records) and I looked at it today (2/8/06), the query would only show me a
total of 3 records (one record for each parcel that is closest to today’s
date). Thanks again for the help.

Matt Franz