| |
You are here: Experts > Computing/Technology > Business Software > Using MS Access > Queries in a table
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
Add to this Answer Ask a Question
|
|
