Tranpose Excel worksheet to Access

What format is the data source for the pivot table in?

Data source points to a table in anther worksheet

Is the table that's the data source not in a more suitable format to import into Access?

When i tried importing to Access,

Since the tables arent perfectly formatted, it isnt perfect with the gaps and etc

The data on the POR Plan sheet could be manipulated to get it into a suitable format for importing to Access.

In fact what you would do is 'unpivot' the data, that's why I'm asking about the format of the source table.

If the source table is in a suitable format it could miss that step out and import directly from the source table into Access.

When tried exporting the source table to Access, the Access program closes.
Maybe its due to the large number of data?

Upon clicking the Data>From Table/Range.. i was prompted to create a table and once i pressed okay and it gave me with a message box that said "A table cannot overlap a range that contains pivot table report,query..."

Am i doing it the right way to unpivot it?

https://www.excel-university.com/unpivot-excel-data/
I was trying to follow this too but i wasnt able to point up the Query editor.

Thanks

Try copying the entire pivot table, as values, to a blank sheet and then following the steps in the link.

PS You should be able to import the table data source into Access, importing via the pivot table is adding an unnecessary step.

This message: "A table cannot overlap a range that contains pivot table report,query..."
implies that in order to get the data into that format it has already been pivoted.  Where is the original data, (pre-pivot)?  That is the table you need to link into Access.

However to give you an idea how to address this, if you had data in an Excel spreadsheet, which could be linked to Access in a format similar to:

MPA    Planning Group    Platform            8/21/17         8/28/17             9/4/17
abc      something             xyz                              9                  14                      12

The way to "transpose" this in Access is create a union query, or a series of append queries to append data into a new table.  The syntax of the union query would look like:

SELECT MPA, Planning Group, Platform, #8/21/17# as WeekEnding, [8/21/17] as AMOUNT
FROM yourTable
WHERE [8/21/17] IS NOT NULL
UNION ALL
SELECT MPA, Planning Group, Platform, #8/28/17# as WeekEnding, [8/28/17] as AMOUNT
FROM yourTable
WHERE [8/28/17] IS NOT NULL
UNION ALL
SELECT MPA, Planning Group, Platform, #9/4/17# as WeekEnding, [9/4/17] as AMOUNT
FROM yourTable
WHERE [9/4/17] IS NOT NULL

Select allOpen in new window

If there are lots of these date columns, the 2nd option, creating a destination table, and then creating a loop in a VBA procedure to loop through the date columns and create an append query, would be a better option.

But your best option is to figure out where the source data of that pivot table is from, and simply link to that data, rather than the pivot table.

Dale

I've already mentioned working with the source data here but apparently there was some problem importing to Access.

Norie,

Yep, saw that, and was reinforcing that recommendation.

But also providing the description of how to actually perform a transpose of data within Access.

you probably can write a macro to copy the cells content from row 13 onward into a temporary worksheet and use it for the importing.

it depends on whether you want to do the data transpose in Access or Excel, but do it in Access it could be faster.

Thanks for the comments!

"Where is the original data, (pre-pivot)?  That is the table you need to link into Access."
- I tried linking but since my original table contains more than 255 fields it got cut off.

So I was trying to transpose it in Excel

Have you tried creating another pivot table, one that doesn't have the extra rows at the top?

By the way, when importing to Access you should get an option to ignore a specified no of rows at the top.

Did you not get that option?

I did not get that option to ignore a specified number of rows.
The only option i got was to make the first row as headings.

And After importing the table to Access, how do i go about transposing it since Access does not have a Record macro option?

Cause i thought it will be easier in Excel since i can record macro to paste special(Transpose)

Lets go back to the original data question.

You indicated that the original data contains more than 255 columns?  Is this "original data" in an Excel spreadsheet, or in an external database (SQL Server, Oracle, MySQL, ...)?

Normally well formatted data will not have that many columns, report (and pivot tables) might, but not raw data.  Figure out where the actual data is coming from and use it.

You can use the Access TransferSpreadsheet method to import data from a spreadsheet into an Access table, and this command allows you to specify a Named Range or even the specific cell range Sheet1!A13:Z500  (although I don't remember the exact syntax for that).  This might be your best option for importing the pivot table to Access.

Amir

How exactly is the source data formatted?

Could you upload a sample workbook?

It gave me the "original data contains more than 255 columns and Excel will only upload the first 255" message box.
But it uploaded fine. I think it was my mistake.

Thanks

And this is how the original data looks like

Amir,

Don't forget to close out your question and select the solution which resolved your problem.