Earliest Date

[Guest]

This post originated from another with subj: Dmax for nearest date
But I think it became over complicated and off track so I'm starting over..

My report is sorted/grouped by people

The data for the report comes from a query. Every record in the query has a
date (dueDate).

Now, I need to show one date for each person, that date has to be the
earliest date (dueDate) for the records of that person.

So, in the query I did a "Sort By - Ascending" on the Date.
Assuming the grouped report would produce the first date for that person.
wrong!

My query does make the dates ascending but not by person, just by all the
dates in general..

How can I show the first date for each person????

Struggling....

 

[Pat Hartman \(MVP\)]

You need to sort by person first and date second.

 

[Scott]

I think what you need to do is create a query and have just the person and
the date field. Group on the person and set the due date to max or min.
You can then link this query to your report query.

 

[Guest]

Don't want to sound bumb but I assum I cannot jsut sort both values in the
same query, I need to use two queries, first being sorted by Person, then a
new one from #1 sorted by Date... Correct???

 

[Guest]

Thank you - Thank you - Thank you.......

I think what you need to do is create a query and have just the person and
the date field. Group on the person and set the due date to max or min.
You can then link this query to your report query.

 

[Pat Hartman \(MVP\)]

No. You need to do the multi-field sort in a single query. If the field
order in your query is not person-date, then either reorder the columns in
the query so that person is first or add the two columns again at the end,
set their sort property, and uncheck the show button so you don't get two
instances of the same field in the resultset.