Others

How to Return the Minimum of Two Columns in a Microsoft Access Query

Steven ParkerSteven Parker Nov 13, 2026 869 views

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.

How to Return the Minimum of Two Columns in a Microsoft Access Query
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the query in Design View

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.

2
Add the calculated field

In the Field row of an empty query column, enter: Minimum: IIf([TickRaDTri1] < [Tick2RaDTri1],[TickRaDTri1],[Tick2RaDTri1])

3
Run the query

Click Run on the Query Tools Design tab and verify that each row shows the lower of the two field values.

Result: The calculated column now returns the minimum of TickRaDTri1 and Tick2RaDTri1 for every record.
Free Microsoft Office alternative

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. 1. Go to the WPS Office download page: Open your browser and visit the official WPS Office website to download the installer.
  2. 2. Install WPS Office: Run the downloaded installer and follow the on-screen instructions to complete installation.
  3. 3. Open existing Office files: Launch WPS Office, open your .xlsx, .docx, or .pptx files, and continue working in a familiar interface.
Free, lightweight Office suite for everyday documents, spreadsheets, and presentationsOpens and saves .xlsx, .docx, and .pptx files with high compatibilityFamiliar ribbon UI for users migrating from Microsoft OfficeRelated Excel data exports often used with Access can be opened and analyzed in WPS Spreadsheets
microsoft office alternative - wps office

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.