I need to query a table that contains results of surveys for various pieces of property. One of the fields will be number, beginning with 1, that indicated which survey this set of data
represents. I will definitely at times want the data from the first survey and that is an easy query.
But what about when I was the last (highest number) query on a property? That is, for the survey number field, rather than putting in a number (because each property has a different nunber of surveys and I don't readily know how many surveys a given piece of property has had) I want to use the equivalent of MAX in Excel. That is, my criteria for that field is to find the record where that field has the largest value.
If I am not clear, I may have 7 entries for various surveys done over time for Property A. Then again, I may have only two surveys or as many as dozens. I know I could easily run a query to show me how many there are so I would no the highest number used for Property A, but that is a pain to do.
So the question is, can I just put Max or Maximum into the query criterion box for the SurveyNumber field and Property A in that field and get the survey results I am looking for, that is the results for the last survey entered?