Others

How to Find the Previous Usage Date for Parts in Microsoft Access

Chanuka GeekiyanageChanuka Geekiyanage Oct 22, 2026 870 views

Question details

The user needs to retrieve the most recent earlier usage date for specific components or parts in a Microsoft Access database.

How to Find the Previous Usage Date for Parts in Microsoft Access
Product
Microsoft Access
Device & OS
not provided
Scenario
Tracking inventory maintenance by comparing current part records with earlier usage dates to determine when a part was last used.
Observed behavior
The goal is to display the current usage date alongside the previous usage date for the same component using an SQL query.
Before you start

Ensure you have a backup of your Access database and familiarize yourself with the SQL View in the Access Query Design tool before running complex queries.

Solution 1Recommended

Use a Correlated Subquery to Find the Previous Date

This method uses a correlated SQL subquery with the MAX() function to find the most recent date prior to the current record's date.

A correlated subquery evaluates the inner query for each row processed by the outer query. In this scenario, it compares the current record's part number with earlier records of the same part.

By filtering for components that are explicitly marked as used and selecting the maximum date that is strictly less than the current record's date, you can isolate the exact previous usage date.

1
Open Query Design

Open your Microsoft Access database, navigate to the 'Create' tab on the ribbon, and click on 'Query Design'.

2
Switch to SQL View

Close the 'Show Table' dialog box without adding any tables, then click the 'SQL View' button located in the top left corner of the ribbon.

3
Insert the Correlated Subquery

Paste the following SQL code into the editor, ensuring your table aliases (MI1 and MI2) properly link the fields: SELECT [Entry number], [Internal Part #], [Date Used], (SELECT MAX([Date Used]) FROM [Maintenance inventory] AS MI2 WHERE MI2.[Internal Part #] = MI1.[Internal Part #] AND MI2.[Date Used] < MI1.[Date Used] AND [Used/Unused] = TRUE) AS [Previous Date Used] FROM [Maintenance inventory] AS MI1 WHERE [Used/Unused] = TRUE ORDER BY [Date Used] DESC;

4
Run the Query

Click the 'Run' button (the red exclamation mark) on the Query Design ribbon to execute the query and view your results.

Use a Correlated Subquery to Find the Previous Date
Best Practice for Naming Fields: Avoid using spaces or special characters (like # or /) in your table and field names. Use CamelCase or underscores (e.g., InternalPartNumber) to prevent complex syntax errors in future SQL queries.
Free Microsoft Office alternative

Need a Lightweight Office Suite for Your Data Exports?

While Microsoft Access handles complex relational databases, you might need a fast, reliable tool for analyzing and reporting on your exported data. WPS Office provides a lightweight, fully compatible alternative to Microsoft Office for your spreadsheets, documents, and presentations.

  1. 1. Export Access Data: In Microsoft Access, right-click your query results and select 'Export' > 'Excel' to save the data as an .xlsx file.
  2. 2. Download WPS Office: Install WPS Office for free to handle your spreadsheet and document needs without a heavy software footprint.
  3. 3. Analyze in WPS Spreadsheets: Open your exported database records in WPS Spreadsheets to easily format, chart, and share your inventory data.
Free and lightweight Office suite for everyday tasksSeamless compatibility with Microsoft Excel (.xlsx) formats for exported Access dataFamiliar user interface with minimal learning curveBuilt-in PDF toolkit for easy sharing of database reports
microsoft office alternative - wps office

Frequently Asked Questions

What is a correlated subquery in Microsoft Access?

A correlated subquery is a query nested inside another query that uses values from the outer query for its evaluation. In this scenario, it checks the outer query's part number to find the specific previous date for that exact part.

Why do I get a syntax error when running the query?

Syntax errors often occur if table or field names contain spaces or special characters (like '#') and are not enclosed in square brackets. Always wrap such names in brackets, for example, [Internal Part #].

Can I achieve this without using SQL view?

While it is technically possible to build complex queries using the visual Design View by adding the same table twice and manually configuring the joins and criteria, writing the correlated subquery directly in SQL View is much more efficient and less prone to configuration errors.