Export pivot table to excel but retain values! (1 Viewer)

L

Lou-Lou-Bell

Registered User.
Local time
Today, 07:34
Joined
Dec 30, 2014
Messages
15
Hi All,
I am so frustrated trying to export my Access pivot table to excel because every time I do, the values (which are text and numbers; i.e. some numbers and some <0.05 format) are changed into a sum, product, blah blah blah. I need to rearrange this data not calculate anything. Does someone have a solution for this? Perhaps Access can help me do this in another way?

Much appreciated!

Lou:banghead:
 
C

CJ_London

Super Moderator
Staff member
Local time
Today, 13:34
Joined
Feb 19, 2013
Messages
16,604
export the raw data to (or link to it from) excel and pivot it there
 
L

Lou-Lou-Bell

Registered User.
Local time
Today, 07:34
Joined
Dec 30, 2014
Messages
15
I imported the data from excel to access, pivoted it in access because it can retain the value but then want to send it back to excel for analysis. If I pivot this in excel, I can't get the values to show up...if you know how to do that it would change my life. :)
 
A

Anakardian

Registered User.
Local time
Today, 14:34
Joined
Mar 14, 2010
Messages
173
You are aware of the option to both format and calculate fields in the pivottable in excel?

Using excel 2010 I find the calculated field menu under PivotTable tools - Options- Fields, items & sets (in the calculations group).

You can get it to make pretty sophisticated calculations for you.
 
C

CJ_London

Super Moderator
Staff member
Local time
Today, 13:34
Joined
Feb 19, 2013
Messages
16,604
by this

pivoted it in access because it can retain the value
Click to expand...
do you mean you used a crosstab query and made or appended to another table?
 
L

Lou-Lou-Bell

Registered User.
Local time
Today, 07:34
Joined
Dec 30, 2014
Messages
15
My issue is always that I want to pivot water quality data and its flag values. I don't want calculations or summaries or anything. What is likely is that I need to learning some coding to do the things I need sometimes. Can anyone suggest an online instructional series on VBA that may be helpful for me in the future?
 
C

CJ_London

Super Moderator
Staff member
Local time
Today, 13:34
Joined
Feb 19, 2013
Messages
16,604
there are plenty of tutorials on the web, I can't say which one is better than another. However you are asking about a pivot table. So far as I am aware, such an object does not exist in Access.

What you have are queries and forms which you can have a pivot view and this view cannot be exported, plus crosstab queries which can be exported.

As previously asked, please clarify what it is you are actually referring to
 
A

Anakardian

Registered User.
Local time
Today, 14:34
Joined
Mar 14, 2010
Messages
173
The question really is, do you want to be able to use the pivot table functions such as slicers on your data or is it a fixed result you need?

For a working pivot table you could:

  • Import the data into access
  • Use queries to make the calculations
  • Store the result in a temporary table
  • Export the contents of the table to an excel sheet where you have your pivot table.
  • Delete the contents of your temporary table.
For a fixed data set it would be mostly like the process above but you would finalize the data in the correct columns and export the result.


Since you already seem to have the import sorted, the option I would use for exporting is "CopyFromRecordSet".
You can specify the exact destination to export your temporary table to.
 
L

Lou-Lou-Bell

Registered User.
Local time
Today, 07:34
Joined
Dec 30, 2014
Messages
15
Thanks all! To answer a few questions, I have no idea how to use crosstabs so pivot tables is what I mean. Imagine one site with 20 parameters and results listed in one column, followed by a second site with 20 parameters (maybe not the same) and results listed, etc. Basically, I need to take 3 columns and put them in order with all the parameters as column headings and all the sites as rows with the values in the table. The pivot table does this and I don't want it to calculate anything BUT I always have a flag field that follows each value which I have to turn into 1,2,0 depending on the flag, and pivot with the data. The pivot tables just look gross and this can take a lot of time when I am looking at results at a site or more from 1960-present...

It would be great if I had a program that looked for the column values paired to the sites for each parameter but maybe that is harder than a pivot table. Pivoting in Access proved too difficult so I just put it back into Excel.

Again, thanks. I will look into a few of your suggestions and look at what cross tabs can do for me. I apologize for my crap explanations.:eek:
 
T

Twincam

Registered User.
Local time
Today, 13:34
Joined
Aug 21, 2014
Messages
34
Do you mean (a picture speaks a thousand words)

Code: 
Site1            Site2
ParameterName1 ParameterName2
value1          value2
flag1             flag2
ParameterName2 parametername3
Value2          value3
FLag2            flag3
ParameterName3 parametername4
Value3          value4
FLag3            flag4
...                    ...
 
 
 
to
 
 
 
Parametername1 Parameterflag1 Parametername2...
Site1           value        flag
Site2           value        flag
Site3
...
 
Last edited:
L

Lou-Lou-Bell

Registered User.
Local time
Today, 07:34
Joined
Dec 30, 2014
Messages
15
site 1 parameter1 flag value
site 1 parameter2 flag value
site 1 parameter3 flag value
...
site 1 parameter40 flag value
site2 parameter1 flag value

INTO
parameter 1 parameter 2 parameter 3
flag value flag value flag value ...
site 1
site 2
site 2
...
 
T

Twincam

Registered User.
Local time
Today, 13:34
Joined
Aug 21, 2014
Messages
34
Have a look at this. A pivot table to summarise the sites and a joined field as the source for a vlookup. Note that the flag vlookup is slightly different to the value vlookup.
 

Attachments

  • Book1.xls
    34.5 KB · Views: 214
L

Lou-Lou-Bell

Registered User.
Local time
Today, 07:34
Joined
Dec 30, 2014
Messages
15
Exactly!! I am so excited to use this the next time I need to pivot my data. :) I don't know why this solution hasn't popped up in my previous searches to make this process easier.

Thank you much!
 
T

Twincam

Registered User.
Local time
Today, 13:34
Joined
Aug 21, 2014
Messages
34
There are so many techniques to use in Excel - the trick is using the right ones in the right order! There may well be better solutions than this one. To add more parameters to the table, just copy/paste the columns in pairs.
 
You must log in or register to reply here.

Similar threads

S
Solved Importing from Excel into a table
Replies
7
Views
256
SwampyNZ
S
R
Solved Importing and calculating the data
Replies
12
Views
499
Robrobby
R
E
export query to Excel file
Replies
1
Views
374
The_Doc_Man
The_Doc_Man
B
Export Access Query to Excel including Conditional Formatting
Replies
12
Views
1,048
apr pillai
apr pillai
S
Solved Issue with acDialog on Adding a Record
Replies
4
Views
497
ebs17
E

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom