Raw Data to Matrix (4-step data transformation process)

[skydivetom]

Hello Experts:

I need some assistance with transforming some raw data into a matrix.

Background:
- In the attached XLS, I illustrate the *transformation process* in four steps.
- Step 1 is the true "raw data". These values can be found in the attached DB (table "tblRawData").
- Step 2 illustrates a simple count (grouped) with field names in ASC order. This view can be found in query "Query - Step 2".
- Step 3 (in Excel) was *manually* created. Here, I simply copied the distinct source references into either column H or I or J or K.
- Step 4 (in Excel) was also *manually* created. Specifically, I now added a "header row" which shows the four distinct source references (row 3 in Excel). Now, however, any populated source reference is replaced with an "x" in the matrix.

Here's what I need some help with (ideally in Access):


- I'd like to automate the process which ultimately allows me to get to "Step 4". I'm not sure if "Step 3" can be skipped based on the query "Query - Step 2".
- If needed, I'm okay with using some form of "helper queries" in order to automate the process. Ideally though, I'd like to keep the number of helper queries/functions to a minimum.
- Finally, please keep in mind that all information (both in Excel and Access files are merely sample data). That is, my actual data source may include 10 different source references and has hundreds of records/values. That said, the transformation process must be *smart enough* to search for the distinct source references when placing them into the header row (Excel Step 4).

Thousand thanks in advance for assisting me (either w/ the required queries or VBA development) in database format!!

Cheers,
Tom

P.S. Attached are the Excel and Access files (in zipped format)

[ranman256]

looks like 3 pivot tables.
record a macro , the run the pivot, then edit the code to allow for generic names and data volume.
then open the raw data, and run the macro

[skydivetom]

ranman256... I think so... would you be willing to provide additional info as to how to create the macros, pivots, VBA, etc?

[ranman256]

in excel, turn on macros:
menu: file, options, (left pane) customize ribbon
(right pane) checkmark DEVELOPER
OK


Now macros are available: menu: developer, RECORD macro
(to start recording)

to make a pivot,
in a sheet with data , put cursor in cell a1,
menu: insert , pivot table
OK, (accept default)
(in far right pane) grab fields (top)
drag them down to the place you want ,either ROWS, COLUMNS , VALUES

STOP RECORDING once you end the pivot

[skydivetom]

ranman256... so, this couldn't be done in Access?

[skydivetom]

Quick follow-up... I get the pivot table in Excel. However, what about the more important steps 3 and 4?

[kd2017]

You could try something like this (untested)

TRANSFORM First(step1.Expr1) AS FirstOfExpr1 
SELECT step1.Fieldname 
FROM   (SELECT tblRawData.Fieldname, 
               tblRawData.Source, 
               "x" AS Expr1 
        FROM   tblRawData 
        GROUP  BY tblRawData.Fieldname, 
                  tblRawData.Source, 
                  "x") AS step1 
GROUP  BY step1.Fieldname 
ORDER  BY step1.Fieldname 
PIVOT step1.Source;  

[skydivetom]

kd2017 -- is this done in a module or a form? Not fully clear how to use your function. Any additional help would be greatly appreciated. Thank you!

[kd2017]

It's an access sql query that transforms the data from your table in "step 1" to the desired output in "step 4"

Go to Query Design to make a new query. On the top left of the ribbon click 'SQL View', and copy and paste the code there. Then hit Run.

[skydivetom]

Wow... this this totally amazing!!!!! Your query solved it beautifully. I'm super impressed!!!!!

[kd2017]

Here is the same thing done using Excel Power Query.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1", "Column2"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each "x"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Column1]), "Column1", "Custom"),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Column2", "FieldName"}}),
    #"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"FieldName", Order.Ascending}})
in
    #"Sorted Rows"

[skydivetom]

Thank you kd2017... 'much appreciated!