By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 994 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

Pivot design to add more (stationary) column headers

P: 1
I have this data:

Company Service Planned Authorized Date
XYZ 1 100.00 50.00 FY12
XYZ 1 100.00 25.00 FY11
XYZ 2 20.00 20.00 FY12
XYZ 3 100.00 25.00 FY11
XYZ 3 100.00 25.00 FY12
XYZ 3 100.00 25.00 FY13
XYZ 4 15.00
XYZ 4 15.00
XYZ 5 54.00 23.00 FY12
XYZ 5 54.00 15.00 FY11
XYZ 6 70.00 70.00 FY12
XYZ 7 125.00 75.00 FY12
XYZ 7 125.00 25.00 FY11
XYZ 8 63.00
XYZ 8 63.00
XYZ 9 48.00 20.00 FY11
XYZ 9 48.00 4.00 FY11
XYZ 3 100.00 25.00 FY14

I need to turn that data into:

Filter by: Company Name
Display:
service (row)
amount planned (column)
authorized total (column)
years (column)
delta (column) = [amount planned] - [amount authorized]
authorized amount per year (values)

I have tried using pivots, but it seems to limit me on the column headers...

The idea is to build this in access so I can import data tables, format into the above, and export the documents in excel. If there is a better way to do this please let me know as well! Please help!
Feb 20 '14 #1
Share this Question
Share on Google+
2 Replies


zmbd
Expert Mod 5K+
P: 5,287
I usually start with good ole Allen Browne's crosstab tutorial when trying to do something beyond what the wizards will accomplish.

See if this information will help: •Crosstab query techniques

If not, please post back with your questions.
Feb 24 '14 #2

P: 8
Might be easier to just query the table from excel as the data source when you create the pivot table, I find managing pivot tables in excel much, much easier.

*EDIT: Ensure you set your connection to "Share Deny None" to avoid locking your db!
Feb 24 '14 #3

Post your reply

Sign in to post your reply or Sign up for a free account.