Results 1 to 3 of 3
  1. #1
    jsimard is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2011
    Posts
    28

    Display Most Recent Date


    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?

  2. #2
    jsimard is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2011
    Posts
    28
    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

  3. #3
    Rhino373 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2011
    Posts
    65
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Get Recent Record without Id or date
    By pyog in forum Queries
    Replies: 5
    Last Post: 04-19-2011, 09:43 AM
  2. Replies: 9
    Last Post: 01-28-2011, 06:05 PM
  3. How to display year season from date entry
    By Allaw212 in forum Access
    Replies: 3
    Last Post: 06-01-2010, 11:11 AM
  4. Replies: 4
    Last Post: 05-04-2010, 03:33 PM
  5. Report to display multiple records by date.
    By af01waco in forum Reports
    Replies: 1
    Last Post: 03-21-2009, 02:12 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums