Query Latest Date

A

Adam

I have two tables, one is a list of business opportunities and the other has
all events (meetings, e-mails, etc.) that are related to the opportunity. I
want to print a report that displays the business opportunity and only the
last event that occurred (the user enters a date field when they enter the
information).

I'm currently use the "Last" function in the query but this is inconsistent
because people periodically enter information into the event table and the
latest date record isn't sorted last (that table is sorted by Event key, not
date).

Is there a way to get just the latest date through the query?

Thanks.
 
W

Wolfgang Kais

Hello Adam.

Adam said:
I have two tables, one is a list of business opportunities and the
other has all events (meetings, e-mails, etc.) that are related to the
opportunity. I want to print a report that displays the business
opportunity and only the last event that occurred (the user enters
a date field when they enter the information).

I'm currently use the "Last" function in the query but this is
inconsistent because people periodically enter information into the
event table and the latest date record isn't sorted last (that table is
sorted by Event key, not date).
Click to expand...

For the EventKey field use criteria with a subquery like this:
IN (Select Top 1 EventKey From Events As tmp
Where tmp.OpportunityID = opportunities.OpportunityID order by
EventDate desc, EventKey desc)
 
Top