Others

Create a Crosstab Report with Dates and Hours in Microsoft Access

Huda QurayshiHuda Qurayshi Nov 22, 2026 868 views

Question details

Create a two-dimensional Access report showing names in rows, dates in columns, and assigned hours at their intersections.

Create a Crosstab Report with Dates and Hours in Microsoft Access
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.
Before you start

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.

Solution 1Recommended

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.

1
Start the crosstab wizard

Open the database in Access, select Create > Query Wizard, choose Crosstab Query Wizard, and select the table or query containing the assignment data.

2
Choose row and column headings

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.

3
Calculate assigned hours

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.

4
Create the report

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.

5
Adjust the layout

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.

Build an Access crosstab query and report
Changing date columns: Crosstab date columns can change as source data changes, but report controls are based on the fields used when the report was designed. You may need to update the report design or use fixed column headings for a predictable date range.
Free Microsoft Office alternative

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.

Review names, dates, and hours in a spreadsheet gridOpen and edit Microsoft Excel XLS and XLSX filesUse a familiar spreadsheet interface for an easier transitionWork with a free, lightweight Office suite
microsoft office alternative - wps office

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.