Export pivot table to excel but retain values!

[Lou-Lou-Bell]

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:

 

[CJ_London]

export the raw data to (or link to it from) excel and pivot it there

 

[Lou-Lou-Bell]

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.

 

[Anakardian]

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.

 

[gopkrtindd]

I imported the data from excel .

 

[CJ_London]

by this

pivoted it in access because it can retain the value

do you mean you used a crosstab query and made or appended to another table?

 

[Lou-Lou-Bell]

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?

 

[CJ_London]

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

 

[Anakardian]

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.

 

[Lou-Lou-Bell]

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.

 

[Twincam]

Do you mean (a picture speaks a thousand words)

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

 

[Lou-Lou-Bell]

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

 

[Lou-Lou-Bell]

So we agree on what I want to end up with...

 

[Twincam]

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.

 

[Lou-Lou-Bell]

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!

 

[Twincam]

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.