Need to select records before a certain date, but not after

[Meryl]

I have a table of individual donations - the keys are the
donor-id and date.

I am trying to write a query that will show all the
donors who have made donations before 6/1/03 but not
after.

Essentially, I need the query to look at the most recent
transaction record for the donor and see if it falls
before 6/1/03. I can't seem to make that happen.

 

[Allen Browne]

1. Create a query into your donation table.

2. Depress the Totals tool bar button (upper sigma icon).
Access adds a Total row to the grid.

3. Drag the DonorID field into the grid.
Accept Group By in the Total row under this field.

4. Drag DonationDate into the trid.
Choose Max in the Total row under this field.

5. In the Criteria row under DonationDate, enter:
< #6/1/2003#

The query returns the donors whose max donation date is less than this date.

Note that if you have a table of donors, some of whom have never donated,
they won't have any record in this donation table.