Limiting Query by date

[Guest]

Hi,

Thanks in advance for your help!

I have 2 tables: Activity and Correspondence. The relationship between the
two is a one-to-many. For each activity, there could be multiple
correspondences. On the Correspondence table the fields are:
- CorrespondenceNumber (primary key)
- Name
- ContactDate
- Results

I want to create a query that for each activity will give me only 1 item
from the correspondence table -- the one with the newest date. And if there
hasn't been any correspondence for an activity I don't want it to show up on
the query results at all. Any ideas?

Thanks!
Heather

 

[Guest]

Your Correspondence table is missing the foreign key the relates it to the
Activity table.

 

[Guest]

There is a foreign key (I just forgot to write it). They're related by the
ActivityID.

Activity Table
- ActivityID** (primary key)

Correspondence Table
- CorrespondenceNumber ** (primary key)
- ActivityID ** (primary key)
- Name
- ContactDAte
- Results

Thanks!
Heather