Discussion Locked

MS Access Query Design

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I need step by step directions on creating a query that will retrieve the lowest and highest values in a field and then post the result in a specific field in a table. This is for a scoring database. The fields with the data are called score and areheld in a separate table that is linked using their record number. The field that holds the result is called the Round1Average. Steps would be greatly appreciated.

3 replies

by dklippert

Rather than create another Table, use a Query.

Create a Query based on the table containing the scores.
Click on the Totals icon on the toolbar
Duplicate the Scores field in the Query.
Click in the Group by box and make one field Max and the other Min.
Eliminate any superfluous fields.

For an Example, go to the Northwind.mdb sample database. Open the Employee Sales by Country Query.
Duplicate the SalesAmount: SubTotal field.
Change one to MaxSalesAmount and the other MinSalesAmountDelete all the other fields, except for Country.
Click on the Total icon and then make the "Group by" of one SubTotal Max, the other Min
Run the Query. (Just hit enter when asked for dates)

This discussion is closed to new replies.