How to Return the Last Working Day of Each Week with an Access Correlated Subquery
Question details
The user needs to construct a query in Microsoft Access that correctly filters records to return only the last working day of each week.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Writing an Access SQL query with a correlated subquery to extract a single maximum date record per calendar week.
- Observed behavior
- The original query returns incorrect dates or all records in the week because the correlated subquery is not properly constrained to match the outer query's year and week.
Ensure you have a working days or calendar table (e.g., WorkDaysCalendar) in your Access database with a valid Date/Time field that can be queried.
Use a Correlated Subquery with the Format Function
Filter your table by comparing the date of each record to the maximum date found for the matching year and week.
A correlated subquery executes once for every row processed by the outer query. By formatting the dates as 'yyyyww' (Year and Week), you ensure that the subquery searches for the maximum calendar date only within the same week as the current row being evaluated.
Open your Microsoft Access database, navigate to the Create tab on the ribbon, and click Query Design. Close the Show Table dialog.
Click the View button on the ribbon (Home or Design tab) and select SQL View from the drop-down menu.
Input the following correlated subquery: SELECT * FROM WorkDaysCalendar AS C1 WHERE calDate = (SELECT MAX(calDate) FROM WorkDaysCalendar AS C2 WHERE Format(C2.CalDate,"yyyyww",1) = Format(C1.calDate,"yyyyww",1));
Click the Run button (red exclamation mark) on the Design tab to execute the query. It will now display only the records corresponding to the last working day of each week.
Return the Week-Ending Date as a Separate Column
Display all records in the table but append a column showing the last working day of the corresponding week.
Need a Lightweight Alternative to Microsoft Office?
While Microsoft Access is highly specialized for database management, daily office and data tracking tasks often do not require complex SQL queries. WPS Office provides a free, lightweight, and incredibly intuitive alternative to Microsoft Office. With WPS Spreadsheet, you can easily calculate end-of-week dates using simple Excel-compatible functions instead of writing complex database subqueries.
- 1. Download WPS Office: Visit the official WPS Office website to download and install the free suite on your device.
- 2. Open WPS Spreadsheet: Launch WPS Spreadsheet and import your working days data directly from an Excel or CSV file.
- 3. Use Easy Date Formulas: Use built-in functions like =MAXIFS() to find the latest date in a week without writing complicated correlated subqueries.
Frequently Asked Questions
Why does my Access subquery return all records in a week instead of just the maximum date?
This happens if your subquery in the WHERE clause lacks the necessary equality check against the outer query. Ensure you are equating the specific date field (e.g., `WHERE calDate = (SELECT MAX(calDate)...)`) rather than just matching the week format.
Can I use DatePart instead of Format for year and week correlation?
Yes, you can use `DatePart("yyyy", calDate)` and `DatePart("ww", calDate)`. However, using `Format(calDate, "yyyyww")` combines both into a single string, resulting in cleaner and often faster SQL syntax.
What does the '1' mean in Format(calDate, "yyyyww", 1)?
The '1' parameter specifies that Sunday is considered the first day of the week. Specifying this parameter prevents incorrect outputs if the system's regional settings vary, ensuring your query behaves consistently.
