Need help recreating Excel sheet (1 Viewer)

ahmed15

New member
Local time
Today, 06:42
Joined
May 22, 2020
Messages
6
I am currently new to using MS Access with 0 experience. I have a pretty complex pivot table in excel with 28 columns and around 60 rows that feeds into another table where we use the Getpivotdata formula to pull data from the pivot into a new table which resides in excel. I want to recreate this by importing the raw pivot table data in MS Access and then utilize and apply something similar to getpivotdata to populate fields in a new table on access. Does anyone have any advice on how I can do something similar to getpivotdata?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:42
Joined
Oct 29, 2018
Messages
21,357
Hi. Welcome to AWF!

I'm sorry to say this, but it sounds like to me you shouldn't do this in Access and simply continue using Excel. Access works better with historical and relational data. It does terrible when you create "spreadsheet-like" tables.
 

ahmed15

New member
Local time
Today, 06:42
Joined
May 22, 2020
Messages
6
Hi. Welcome to AWF!

I'm sorry to say this, but it sounds like to me you shouldn't do this in Access and simply continue using Excel. Access works better with historical and relational data. It does terrible when you create "spreadsheet-like" tables.

Aha thank you DBguy! Unfortunately it is not up to me I am a coop student at a bank and essentially they want me to create this as a mock for what i believe it should look like when they create it for a SQL server. They want to take raw data that is updated, insert it into a new table, then return a three column output.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:42
Joined
Oct 29, 2018
Messages
21,357
Aha thank you DBguy! Unfortunately it is not up to me I am a coop student at a bank and essentially they want me to create this as a mock for what i believe it should look like when they create it for a SQL server. They want to take raw data that is updated, insert it into a new table, then return a three column output.
Hi. Good luck with it. Whoever gets tasked to maintain it, will probably have a hard time and hate whoever created it (I'm just guessing).
 

Minty

AWF VIP
Local time
Today, 10:42
Joined
Jul 26, 2013
Messages
10,353
This is certainly something that can be achieved, but we would need t see some sample data, (your expected input) and the desired results.
You would need to include enough data to see all possibilities.

And it may or may not be easier in SQL server than Access.
 

ahmed15

New member
Local time
Today, 06:42
Joined
May 22, 2020
Messages
6
Pivot table 1:
Channel (pivot filter)Transaction type(pivot filter)Product 1Product 2Product 3
Channel 1Transaction type 1XXX
Transaction type 2XXX
Transaction type 3XXX

Getpivotdata Table 2:
Cost Driver 1 (Mapped from channel + Transaction type + Product)Cost driver 2 (Mapped from channel + Transaction type + Product)Cost driver 3 (Mapped from channel + Transaction type + Product)Cost Driver (Mapped from channel + Transaction type + Product)
product 1XXXX
product 2XXXX
product 3XXXX

Unpivot/flattened table 3:
ProductCost DriverVolume
Product 1Cost driver 1X
Product 1Cost driver 2X
Product 1Cost driver 3X
 

ahmed15

New member
Local time
Today, 06:42
Joined
May 22, 2020
Messages
6
This is certainly something that can be achieved, but we would need t see some sample data, (your expected input) and the desired results.
You would need to include enough data to see all possibilities.

And it may or may not be easier in SQL server than Access.
Take a look minty, this is basically the objective to achieve in access. Currently I just need to create the second table in access and copy and paste the first pivot
 

Minty

AWF VIP
Local time
Today, 10:42
Joined
Jul 26, 2013
Messages
10,353
Okay - I don't really see quite enough of the data, but this is how I would do this. (And it won't be simple to automate)
If you have excel as a tool anywhere in the process, and I'm guessing you do if the source is table 3 above, then you can automate a Power Query to unpivot that data into a normalised structure. You can then import that into Access as a normalised data set.

Doing this directly in access is not simple, the other option is to use the unpivot feature in SQL Server https://docs.microsoft.com/en-us/sq...using-pivot-and-unpivot?view=sql-server-ver15
 

ahmed15

New member
Local time
Today, 06:42
Joined
May 22, 2020
Messages
6
AHAHA THAT IS SO FUNNY because that is exactly what I did but they don’t want that they want all 3 tables to reside in access... 😕 so basically they want me to copy and paste the pivot from table 1 into access. Somehow convert it into table 2 by mapping the values to the products and cost drivers which are new fields (similar to getpivotdata on excel) then convert that into table 3. I will have to write lines of SQL to unpivot since he does not want power query used 😟. My main problem is how do I get from table 1 to table 2 in access?
 

June7

AWF VIP
Local time
Today, 02:42
Joined
Mar 9, 2014
Messages
5,423
A UNION query can rearrange table 1 to normalized structure shown in table 3. Although not having channel repeated on each row would be an issue. Then do a CROSSTAB to get table 2.

What is the raw data that table 1 pivot is based on?
How many pairs of channel/transaction would be possible?
If you want to provide workbook for analysis, follow instructions at bottom of my post.
 
Last edited:

Minty

AWF VIP
Local time
Today, 10:42
Joined
Jul 26, 2013
Messages
10,353
Well, I would be tempted to go back to them and say Access isn't the tool, in this instance to do the job efficiently or sensibly.

It wouldn't be horrible in SQL but it wouldn't be pretty.
You could just import the original pivot into an Access table, upload that to a linked SQL table and apply a stored procedure.
Think it would take a while to get the data extract working nicely though.
 

ahmed15

New member
Local time
Today, 06:42
Joined
May 22, 2020
Messages
6
Hey everyone thanks for the help! I believe I was able to completely figure it out. Thank you for the assistance
 

Users who are viewing this thread

Top Bottom