Query for Most recent Date

[WaterDoc]

I have a main table with municipal information (muniID, name, contacts, etc) that are specific to each municipality. I have another table containing meeting information including comments and a date for each meeting that has been held with each of the municipalities. (1 municipality to many meetings relationship)

I would like to make a report with some of the info from the main table for each municipality and only the most recent meeting date for each. I've got a query set up with the fields from the main table I'd like and meeting dates from my meeting table. Of course when I run the query I get all the meeting dates ever held (sometimes 4 or 5 for one municipality with the same municipal information repeated).

How do I get only the most recent meeting so I know who I haven't met with in a while?

 

[jzwp22]

You will need to create an aggregate query using the meeting table and group by muniID, something along these lines

SELECT muniID, Max(meetingdatefield)
FROM meetingtable
GROUP BY muniID

Save the above query; I'll call it Q1 (you can name it something more appropriate)

Now create another query and include your meeting table and Q1. Make joins between the muniID fields in both sources and the meetingdate/maxofmeeting date fields, select the other fields you want (make sure to include the muniID. Save this query. I'll call it Q2

Make a third query that joins your main table and Q2, join by muniID field and select the fields you want.

 

[WaterDoc]

Hey the pointers worked. Got it working with the last meeting date and other associated info I needed in the report. Thanks for your help! It kept me from spinning my wheels...

 

[jzwp22]

You're welcome!