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?
LVL 1
David BigelowStaff Operations SpecialistAsked:
Who is Participating?
 
PatHartmanCommented:
David, even though the data is housed in Access, it occasionally makes sense to use Excel to create a report.  100 columns across the top is going to be very difficult to do with Access.  it just doesn't have the shrinking feature that Excel does which enables you to "fit" wide reports to a fixed number of pages.  With Access, you get a maximum width (22" I think) and that is that.  If you can't make it look nice in 22", too bad.
0
 
Tj aCommented:
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.
0
 
Dale FyeCommented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
PatHartmanCommented:
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.
0
 
Rob HensonFinance AnalystCommented:
Yes, Excel does allow for diagonal alignment. It is accessed from the Format Cells dialogue. Screen shot below:

Thanks
Rob H
Diagonal Alignment
0
 
David BigelowStaff Operations SpecialistAuthor Commented:
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.
0
 
Martin LissOlder than dirtCommented:
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.
0
 
David BigelowStaff Operations SpecialistAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.