Need help with query

[Jennifer]

I'm stuck on this one. I have two tables - tblContacts and tblHistory.
There is a one to many relationship (1 contact to many history records)
linked on ContactID.
In the tblContacts there is an AccountMgr field that is the same info as a
CreateUser field in tblHistory. In English this is what I need.

I need to select all the info in tblContacts and tblHistory where the
AccountMgr = createUser sort by ContactID ASC and tblHistory.CreateDate DESC.

This is where is gets tricky....

I need it to only return the first row for each Contact ID which would
return the last time the Contact was contacted. (The createDate represents
the date the history record was created....or in other words the last time
the Contact was called.) In additon I only want it to return a record if
that last createDate is older than 3/22/08. So I only want to see the record
if the last time the Account Manager called the person was older than 60 days.

I just can't seem to get it to return the top 1 history record for each
contact. Using the top # doesn't work. Using the group by doesn't seem to
work.

I even tried combining the info into one table and working with it that way.

Obviously this needs a subquery but I just can't seem to get this one.
Everything I try bombs.

Any suggestions anyone?

I am more than happy to send more info if anyone cares to attempt this one.

Thanks in advance.

Jennifer

 

[Michel Walsh]

You can try one of the four techniques illustrated at
http://www.mvps.org/access/queries/qry0020.htm to get the latest contacted
time, for each ContactID. The example is about getting the latest borrower
for each books, in a library.



Vanderghast, Access MVP