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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 FyeOwner, Developing Solutions LLCCommented:
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
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
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Page Layout Software

From novice to tech pro — start learning today.