Home How do I change a list of customers with multiple lines, and details into a single account line with categories

Questions

How do I change a list of customers with multiple lines, and details into a single account line with categories

February 2, 2022

Example Table, Turn this

CustID	CustName	SalesRepName	SalesRepSpecialty
1	ACME Corp	Steve P	Servers
1	ACME Corp	Susy Q	Network
1	ACME Corp	John D	Storage
2	MicroStuff	Auntie Irma	Network
3	O-Rickle	Daddy Larry	Servers

and turn this into

CustId	CustName	Server	Network	Storage
1	ACME Corp	Steve P	Suzy Q	John D
2	MicroStuff		Auntie Irma
3	O-Rickle	Daddy Larry

Is this possible to do in MS Access?

>Solution :

You can use the Crosstab Query Wizard in Access to create a TRANSFORM statement (Microsoft Access SQL):

TRANSFORM Max(Turn.SalesRepName) AS MaxOfSalesRepName
SELECT Turn.CustID, Turn.CustName
FROM Turn
GROUP BY Turn.CustID, Turn.CustName
PIVOT Turn.SalesRepSpecialty;

To get the ordering of the columns right, you can append this clause manually (before the ;):

IN (Servers, Network, Storage)

This also has the advantage that these columns will be created even when the corresponding SalesRepSpecialty is missing; however, new values will not automatically be appended. If you create an Access Report based on this query, then you need a fixed set of columns to be able to add a field for them (unless you do some complex dynamic stuff in VBA).