Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2012
    Location
    Norfolk, VA
    Posts
    2

    Unanswered: Query to Return Only Most Recent Entry

    I have a data base in Access 2010 that supports an annual sailing regatta. There are three tables that are relevant to this question; 1) tblSkippers, containing a key field skipperid, name, address, phone numbers, email addresses etc.; 2) tblBoats, containing a key field Boatid, boat name, boat type, sail number, etc.; and 3) tblEntries, containing a key field Entryid, the regatta year, Skipperid, Boatid, and other data not relevant to this question such as fleet entered and handicap rating. In essence an entry consists of a year, boat ID and skipper ID. It is common for skipper ID and boat ID to be used regatta after regatta, with only the regatta year making the entries unique.

    I want to send a reminder email message to every skipper who has entered in any of the previous three regattas. I wrote a query that selects the applicable entries and also returns the skipper's name and email address from tblSkippers. The problem is that I get duplicate skipper names and email addresses because many skippers enter every year. I tried setting Unique Values to "Yes" and clearing the "show" check box for the email field. That 1) didn't work, and 2) would defeat the purpose because I need the email address for my mail merge. I've found articles that imply that I should change "Select" to "Select Distinct [email]", but I can't find where I should do that.

    Can anyone point me in the right direction?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    switch from query design to sql view....
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2012
    Location
    Norfolk, VA
    Posts
    2

    Better explanation of problem

    Thanks, I was able to get it to work as designed, but that doesn't fit my needs. The problem is that if a skipper enters a different boat Access treats it as two distinct records. What I'm trying to do is to get the most recent record for each skipper. If I was working with a flat file it would be easy. Here's pseudocode for what I would do:
    Sort file by skipper, then by year
    goto record 1
    Let teststring equal emailstring
    For record=2 to end of file
    If teststring = emailstring delete record, else let teststring equal emailstring
    Next record

    Of course I don't want to delete the records in Access, I only want to create a query so I can do a mail merge. I can't believe that something so simple to do in a flat file could be this hard to do in Access. I suspect that I just have a brain freeze about this. Can anyone help me?

  4. #4
    Join Date
    Mar 2015
    Posts
    31
    Hello projectpro,

    Have you thought about using the "FirstOf" criteria in your query design ? That would give you the most recent data i.e the last boat that was used.
    And then if you don't want to delete the records in access i would suggest maybe hiding the irrelevant ones instead of deleting them.

Posting Permissions

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