How to Return the Minimum of Two Columns in a Microsoft Access Query
Question details
The user needs a Microsoft Access query expression that returns the smaller of two fields per record instead of the aggregate Min function result.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Creating or editing an Access query that compares [TickRaDTri1] and [Tick2RaDTri1] and returns the minimum for each record or for the whole dataset.
- Observed behavior
- Access's Min function aggregates values across records and does not directly compare two fields in the same record.
Confirm the actual field names in your query and verify that both fields use comparable data types, such as numbers or dates, before adding the calculated field.
Use an IIf Expression to Return the Smaller Value in Each Access Record
Use this solution when you need the minimum of two columns row by row in a standard Select query.
The Min function in Access is an aggregate function, so it works across many records. To compare two fields in the same record, an IIf expression evaluates the condition row by row.
In the Access Navigation Pane, right-click the query and choose Design View, or create a new query in Design View and add the source table.
In the Field row of an empty query column, enter: Minimum: IIf([TickRaDTri1] < [Tick2RaDTri1],[TickRaDTri1],[Tick2RaDTri1])
Click Run on the Query Tools Design tab and verify that each row shows the lower of the two field values.
Use a Totals Query to Return the Overall Minimum Across Two Columns
Use this solution when you need a single smallest value from both columns across the entire query result.
Use WPS Office as a Free Microsoft Office Alternative for Spreadsheet and Document Work
WPS Office does not include a Microsoft Access database replacement, but it is a free, lightweight Office suite that opens and edits the .xlsx, .docx, and .pptx files you work with alongside Access. The familiar ribbon interface makes it easy to switch from Microsoft Office.
- 1. Go to the WPS Office download page: Open your browser and visit the official WPS Office website to download the installer.
- 2. Install WPS Office: Run the downloaded installer and follow the on-screen instructions to complete installation.
- 3. Open existing Office files: Launch WPS Office, open your .xlsx, .docx, or .pptx files, and continue working in a familiar interface.

Frequently Asked Questions
Why doesn't Access Min return the minimum of two fields in the same record?
Min is an aggregate function that returns the smallest value across many records. To compare two fields in the same record, use an IIf expression such as Minimum: IIf([TickRaDTri1] < [Tick2RaDTri1],[TickRaDTri1],[Tick2RaDTri1]).
How do I find the overall minimum value from two columns across all records?
Use a Totals query and compare the aggregate results: OverallMinimum: IIf(Min([TickRaDTri1]) < Min([Tick2RaDTri1]), Min([TickRaDTri1]), Min([Tick2RaDTri1])).
What should I do if one of the columns contains null values?
The comparison may return null if either field is null. Wrap the fields with Access Nz() to convert nulls to 0 or another default before comparing.
Can WPS Office open and run Microsoft Access queries?
No, WPS Office does not include Microsoft Access or an Access query engine. Use Microsoft Access for .accdb/.mdb queries; WPS Office can open and edit the companion .xlsx, .docx, and .pptx files.




