CODEWITHSUNDEEP
ms-accessreportcrosstab
Cory
Cory

Reputation: 144

Multiple values in crosstab report MS-Access

I'm working on a database to replace the hundreds of monthly excel spreadsheets keeping track of our employee performance reports. My boss wants to be able to view the data in a weekly summary view. The current excel table doing this looks similar to the following below:

        |----Sun #DATE#-----|----Mon #DATE#-----|...    |Sat... |   Totals|
TEAM-1  |S1 |S2 |S3 |S4 |S5 |S1 |S2 |S3 |S4 |S5 |...    |...    |...
Empl-1  |## |## |## |## |## |...
Empl-2  |...
Empl-3  |...
...
TEAM-SUM|## |## |## |## |## |...->|SubTotal|

TEAM-2  |...
...

Daily-SUM|...->|GrandTotal|

I have a table that stores the Sx values for each recorded day. The fields are RowID EmployeeID TeamID DateRecorded Sx...

I've tried getting a layout similar to the above in a report, but it's eluding me. I tried a crosstab query, but it only allows one value and one column header, so this doesn't work to recreate ~5 values under each column header. Is there a way to recreate the layout in an access report given the table above, or is there a way to rework the layout to convey the same information?

Thanks for your help!

Upvotes: 0

Views: 489

Answers (1)

Joe R.
Joe R.

Reputation: 2042

You dont necessarily need to create an Access report, you can also create an Access form that has a datagrid subform that can mimic your Excel report layout. Dropdown lookup fields can be placed in the upper header form for dynamically building the queries to populate the datagrid subform with the desired results.

You will also want to create another form for entering and editing the data. This form will also provide better data validation controls that minimize or can totally eliminate data entry errors.

Up front, It will help if you can edit your question to include the tables, fields and relationships you created for storing the data you imported from Excel. Keep in mind that most Excel data is stored in flat un-normalized tables, and you want to take advantage of the relational database architecture that Access offers.

Upvotes: 1

Related Questions