Hi
I have a query bringing together information from lots of tables for a mail merge.
The bulk of the fields come from 2 tables, tblPerson and tblPropHistory. A Person can have many entries in PropHistory.
I need the query to return a single result, that being the the most recent property a person has occupied.
I have set up qry1 to return the correct Person (often this returns 3 or 4 results as the person has had 3 or 4 properties)
I have set up qry2 based on query 1, and have enabled total. In the field DateMovedIn i set the total to Max, expecting this to just return the result with the most recent DateMovedIn.
For some reason it returns all the properties for that Person (the same as the qry1 output).
Please can you advise where i'm going wrong.
Thanks
Kev
I have a query bringing together information from lots of tables for a mail merge.
The bulk of the fields come from 2 tables, tblPerson and tblPropHistory. A Person can have many entries in PropHistory.
I need the query to return a single result, that being the the most recent property a person has occupied.
I have set up qry1 to return the correct Person (often this returns 3 or 4 results as the person has had 3 or 4 properties)
I have set up qry2 based on query 1, and have enabled total. In the field DateMovedIn i set the total to Max, expecting this to just return the result with the most recent DateMovedIn.
For some reason it returns all the properties for that Person (the same as the qry1 output).
Please can you advise where i'm going wrong.
Thanks
Kev