Query to select a record with highest value

[ByteMyzer]

There is a way to put this together using the expression builder. Per your requirement as stated below, I've attached a screenshot to show how this may be done in the query-building screen.

OK, getting there. The above selects the Max by Group. The end result I am trying to get to is a Query By Form where a check box on my form (cbLatestIssues) will cause the latest issue for each title to be shown.

If Check Box ticked:

Title1.......... 003
Title2...........002

If Check Box NOT ticked:

Title1...........001
Title1...........002
Title1...........003
Title2...........001
Title2...........002

The second expression in the Expression Builder popup with the SELECT statement needs to be typed in by hand (with the appropriate Table/Field name substitutions, of course). This second expression is known as a Subquery, or a query-within-a-query, and is a useful and versatile means of filtering information. As you become more conversant with query-building in Access, as well as the SQL behind the queries, I'm sure you will find many more uses for the Subquery method of filtering data.

 

[DubaiDave]

ByteMyzer,

After a little fiddling to make it work with my other expressions, this worked fine.

Thank you very much for taking the trouble to generate that screen shot.

Dave

 

[ByteMyzer]

You're very welcome, I'm glad it worked for you.