Expert: Scottgem - 11/2/2009

Question
Scott,

I have an "Events" table uses a separate "Companies" table for its company source (using Companies.ID for linking and Companies.Company for name display) and houses all of our meeting data.  Each meeting contains an event date, a company field (as mentioned above), an event type and attendees.  Separately, in my Companies table, there is a yes/no field titled "Watch List."  I'm trying to generate a query that will take the Companies on the "Watch List" and pull the most recent (ie MAX) date from the Events table to let me know the last time I had contact with each of those companies.  I'm not able to get the results I want.  When I try to use a join equating the Company names (Events.Company = Companies.Company), I get no results.  When I try to equate the Companies.ID to Events.Company, I get a type mismatch error.  Not really sure how to proceed.  Thanks.

cheers,
DJ

Answer
You need to create a Top N of Group query. This article tells you how. You want to create the TOP 1 of each company sorting by event date in descending order. Then join that query top your company query on CompanyID.

Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA