Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2009

    Unanswered: Selecting Latest Entry

    I have an Access DB where one parameter records the current status of our interactions with a contact. In the past I have had a single field which we would change as status changed - e.g. pursuing, non-active, dialog ongoing, etc. My problem is that we loose the history every time we change the status.

    To solve this, I have created a separate status table (tblStatus) with all the options that I can think of, and then created a table to join tblContacts with tblStatus. In the join table I added date and memo fields to make notes with each status and record the date the status changes.

    Now my question: I would like to select the latest status from the list of historical statuses, and have that be the most current status for the contact when doing a search. What is the best way to do this? Thank You.


  2. #2
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Surrey, UK
    Provided Answers: 18
    SELECT [Status], [Notes], MAX([NotesDate]) AS LastContact...
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts