Display Most Recent Date

[jsimard]

I have a form where I enter in a client ID and on that form there is a date field for their next visit. Each time the client visits a record gets inputted and a new "next visit" date gets enetered. This field gets filled in after each visit and is not just "updated or replaces the old date". I would like to create a report that when grouped by the client id will only display their most recent "next visit" date and not display all the past dates. Is this possible?

[jsimard]

I guess I should clarify somethings....on my form I have more than one "next visit" text field. We are trying to track the next visit for 4 different types of service so I have a "next visit" text field for each of those. Is there a way to create a report that will display the client id, name and next visit for each service all on one line?

Example:

Id: Client Name: Next Visit 1 Next Visit 2 Next Visit 3 Next Visit 4
1 Jane Smith Aug 2011 Nov 2011 Oct 2011 Dec 2011

[Rhino373]

This all depends on what your structure looks like. I picture 3 tables. A "Client Table" with Cutomer ID and Client Name. A "Service" table with Service and Service ID and finally a "Next Visit" table with Next Visit, Service ID and Customer ID. This is the best method because it allows you the flexibility to add services later. The other structure would be the same "Client Table" but then only a "Next Visit" table with Next Visit 1, Next Visit 2, Next Visit 3, Next Visit 4 and Customer ID. (each visit relating to a specific service).

Either way you basically make a totals query of the Visit Date and take the Max(). and Group by Customer ID (doing it the way I suggest makes you group by Service as well). Then your second query just makes a match on ID and you'll have the newest visit.