How to Find the Previous Usage Date for Parts in Microsoft Access
Question details
The user needs to retrieve the most recent earlier usage date for specific components or parts in a Microsoft Access database.

- 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.
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.
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.
Open your Microsoft Access database, navigate to the 'Create' tab on the ribbon, and click on 'Query Design'.
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.
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;
Click the 'Run' button (the red exclamation mark) on the Query Design ribbon to execute the query and view your results.

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. Export Access Data: In Microsoft Access, right-click your query results and select 'Export' > 'Excel' to save the data as an .xlsx file.
- 2. Download WPS Office: Install WPS Office for free to handle your spreadsheet and document needs without a heavy software footprint.
- 3. Analyze in WPS Spreadsheets: Open your exported database records in WPS Spreadsheets to easily format, chart, and share your inventory data.

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.




