Create a Crosstab Report with Dates and Hours in Microsoft Access
Question details
Create a two-dimensional Access report showing names in rows, dates in columns, and assigned hours at their intersections.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- The user wants an Excel-like report with approximately 90 day columns for each person.
- Observed behavior
- The desired report displays row headings, date column headings, and corresponding hour values.
Identify the table or query containing each person's name, assignment date, and hours. Check that the date field contains the intended dates and that the hours field is numeric.
Build an Access crosstab query and report
Use a crosstab query to group hours by person and date, then use it as the source for a report.
A crosstab query summarizes records; it is not updateable. Edit the underlying table or source query to change the assignments.
Open the database in Access, select Create > Query Wizard, choose Crosstab Query Wizard, and select the table or query containing the assignment data.
Select the person's name as the row heading and the assignment date as the column heading. If prompted for a date grouping, choose an option that preserves individual dates rather than grouping by month or year.
Select the hours field as the value and choose Sum to combine hours when more than one record exists for the same person and date. Finish the wizard and save the query.
Select the saved crosstab query in the Navigation Pane, then choose Create > Report Wizard. Select the fields to include, finish the wizard, and review the report in Print Preview.
In Page Setup, try landscape orientation and adjust margins or column widths. If approximately 90 date columns do not fit legibly, filter the source to shorter date ranges and create separate report sections or exports.

Review exported crosstab data in WPS Spreadsheets
WPS Office is a free, lightweight Office alternative with a familiar interface. Export Access query results to an Excel file, then open them in WPS Spreadsheets for review and spreadsheet-based editing. WPS does not run Access queries or reports.

Frequently Asked Questions
Can I edit assigned hours directly in an Access crosstab?
No. A crosstab query is not updateable. Open the underlying table or an updateable source query to edit the hours, then rerun the crosstab.
How do I handle 90 date columns in a printed report?
Use Print Preview and Page Setup to test landscape orientation and narrower margins. If the columns are still too small, filter the source query to shorter date ranges and print separate reports.
Why might new dates not appear correctly in my report?
A crosstab can produce different columns as dates change, while report controls remain tied to the fields used in its design. Update the report design or specify fixed column headings for the intended date range.
Can I open the crosstab results in WPS Spreadsheets?
Yes. In Access, select the crosstab query and use External Data > Excel to export its results. Open the resulting Excel file in WPS Spreadsheets; the exported file does not retain the live Access query.




