excluding dates

[Todd]

Hello all again

I have a table that tracks inspections.
I only need to see the most current inspection due though on my report.
My date fields are as follows.
Date = date of inspection
Next Due = when the next inspection is due.
Next month due = add's 1 to the next due, so I can create a report of what
is due next month.

So, after an individual has been here for over 8 months, they will have two
records in this table.
I do not need to see the older records, as the only one that I care about,
is the most current one.
I do need to be able to see the other records sometimes, just for the
history on someone.

Thanks

 

[Allen Browne]

Simplest approach will be to create a query that returns the most recent
inspection date for each item. Then build another query that uses that one
too.

1. Create a query using your table.
In query design, depress the Total button on the toolbar.
Access adds a Total row to the design grid.

2. In the Total row under the individual's field, accept:
Group By

3. In the Total row under your date field, choose:
Max

4. Save the query. Close.

5. Create another query using this one as an input 'table', as well as your
real table. In the upper pane of query design, join the 2 on both the
Individual and the Date fields (2 join lines.)

6. Save this second query, and use it as the source for your report.

 

[Todd]

Allen

Thanks. This did not work. No errors, but all the data is presented.
The date next due field is computed using a date add function from the date
function. Not sure if this changes things with this query, and the answer
you provided.

Thanks

Todd

 

[Allen Browne]

Post the SQL statement for the 2 queries.

Tell us which is the date field name that you want to find the most recent
date from.