Access Report Using CrossTab Quary for people

[CLCoop]

Tyring to build a report that shows Organization and position that people are in.

For example:
There is a table that shows all employees (admin) and them in the respective positions

Sort ORder	Org	Big Boss	Manager	Worker
1	South Group		me
1	South Group			vacant
2	North Group	Tom
2	North Group		Sara

Using a query I've done a Totals (group) but cant seem to get following output.

1	South Group		You	Vacant
2	North Office	Tom	Sara

Can't seem to get a query to do a group on office then put all the name in one row... I've tried using Group by on Organization Description and Sort then Boss, Manger, Worker set at last, first and group...
I get something like this.. UGH please help

1	The Office			Vacant
2	North Office			North Worker

I've tried then building a report that would all so do this to no avail.

 

[alansidman]

If the following is an acceptable format then use the Mcode shown below from Power Query

Book1
	A	B	C	D	E
1	Sort ORder	Org	Manager	Worker	Big Boss
2	1	South Group	me	vacant
3	2	North Group	Sara		Tom
Sheet2

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sort ORder", Int64.Type}, {"Org", type text}, {"Big Boss", type text}, {"Manager", type text}, {"Worker", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Sort ORder", "Org"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"

 

[CLCoop]

quick question
Excel.CurrentWorkbook... this is a table within Access so should I put source = Table.tablename() and should this VBA code best go behind button to execute or on loading a form? thinking push a button have this code run then open a form with the data...

Thank you for your help..

 

[alansidman]

No. Actually, you manually import table into Power Query. Click on the link in my signature to learn more about Power Query. In the PQ menu on the Data Tab, you will import Databases and select Access and then the particular table/query.

This is not VBA. This is Mcode which is the language for Power Query.