Link to home
Start Free TrialLog in
Avatar of David Bigelow
David BigelowFlag for United States of America

asked on

What are some options for displaying a report similar to a spreadsheet?

Our managers use a spreadsheet that lists some 100 skills across the top, with a side listing of employees, each with a checkmark, X, in the appropriate column according to the skills they know. To save space, the skill names are turned at steep diagonal in the spreadsheet.

The main purposes of the spreadsheet are:
1. To see how many are trained in a given skill, for workload and workforce planning
2. To see in which workloads a given employee is trained.

I could layout a report in landscape orientation that would give all that information, but how can I save space? To type out each skill horizontally would take a lot of room. Is it possible to turn the group headings diagonally in a report? That would allow me some space efficiency. I could export to Excel, but that seems like a lot of formatting each time. Possibly write a macro to automate the layout format after exporting.

Have any of you encountered a similar situation in which made a different layout but still had the information to meet the purposes of the report?

What are some options for meeting these report purposes?
Avatar of Tj a
Tj a

Hi David,

I think you might consider keeping the worksheet as it is but making a pivot table from it and putting it in a separate sheet. That way you can roll up the columns so you don't have to see ALL the data at the same time (thus saving space as you said). There will be a + symbol to unroll the data when you want to see it and a - symbol to hide it. Another advantage here is that the totals look clearer and you have added/advanced options when processing the data in that sheet as opposed to using a regular table. I hope that helps.
Avatar of Dale Fye
In Access, you could use a datasheet, which allows you to freeze several columns on the left and size columns as you see fit.

I would not store the data in that format, but would instead use a crosstab query to get the data into that format.
Unfortunately the datasheet view won't let you make the column headers vertical.  You would need to use continuous view to do that.

Sometimes, it just makes sense to export a report to excel where the formatting options for wide reports are better.  Use a crosstab query to pivot the data and export the crosstab query.  You can manually adjust the column headings in the spreadsheet or if you want to automate the whole thing, then after you use TransferSpreadsheet to populate the workbook, open it with Access and format the headers using VBA.  If you don't know how to do this, the first step should be to open the unformatted report in Excel and turn on the macro recorder.  Then format the header, etc.  Once it is done, stop the recorder and copy the generated code into Access.  You will need to make minor modifications but the essence of what you need will be there.
Yes, Excel does allow for diagonal alignment. It is accessed from the Format Cells dialogue. Screen shot below:

Thanks
Rob H
User generated image
Avatar of David Bigelow

ASKER

Rob and Takunda, I saw earlier that my post wasn't very clear. After my numbered list, I transitioned from describing the current spreadsheet to seeking options about how to lay that out in an access database report. The data will be housed permanently in a database, where it can be used for other reasons, as well.
I've requested that this question be closed as follows:

Accepted answer: 125 points for Takunda Jora's comment #a40645006
Assisted answer: 125 points for Rob Henson's comment #a40646511
Assisted answer: 125 points for Dale Fye (Access MVP)'s comment #a40645050
Assisted answer: 125 points for PatHartman's comment #a40645563

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
ASKER CERTIFIED SOLUTION
Avatar of PatHartman
PatHartman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I decided to go this route. I got to thinking about the fact that most of the users are not accustomed to working with Access. Having a report with the information in Excel will give them a bridge of something familiar to start working with and then transition into the actual database to enter new data. I've used the linking properties of Excel to link to the current tables and queries needed and made a pivot table from them in Excel, as opposed to having the pivot table in Access.