Crosstab Query Help

[gmatriix]

Hello All,

I have a table that have multiple entries and I am trying to get it all on one line base off of account number.

The way the table was set up (not me) repeats the data when I run a query against it. I know I possibly can run a cross tab query that might clear this up but have a bit of trouble with it.

Here is a example how the current query is coming out.

Account Number	Status	Type	Name
123456	Active	Car	Joe Shmo
123456	Active	Car	Jane Shmo
123456	Active	Car	Jim Shmo
654321	Cancelled	Bus	Bob Change
654321	Cancelled	Bus	Bill Change

This is what I am trying to accomplish

Account Number	Status	Type	Name 1	Name 2	Name 3
123456	Active	Car	Joe Shmo	Jane Shmo	Jim Shmo
654321	Cancelled	Bus	Bob Change	Bill Change

Any Ideas?

Thanks

CG

 

[theDBguy]

Hi. Can you post a sample db with test data? I think this will need some extra steps. For example, you'll have to somehow add another column to designate Joe Shmo and Bob Change are Name 1 and Jane Shmo and Bill Change are Name 2. Without that designation, a simple Crosstab wouldn't be able to figure out what columns to generate.

 

[plog]

What's the big picture? Do you really need a new field for every [Name] value? Or do you simply need every [Name] value in one record?

A cross-tab may not be the best solution, instead the ConcatRelated(http://allenbrowne.com/func-concat.html) function might work.

 

[strive4peace]

hi gmatriix,

a Crosstab query isn't going to get the results you want.

This SQL:
TRANSFORM First(MyTable.Name) AS FirstOfName
SELECT MyTable.[Account Number], MyTable.[Status], MyTable.[Type]
FROM MyTable
GROUP BY MyTable.[Account Number], MyTable.[Status], MyTable.[Type]
PIVOT MyTable.[Name];


results in this display:

crosstabData_Crosstab

Account Number	Status	Type	Bill Change	Bob Change	Jane Shmo	Jim Shmo	Joe Shmo
123456​	Active	Car			Jane Shmo	Jim Shmo	Joe Shmo
654321​	Cancelled	Bus	Bill Change	Bob Change

Perhaps what you should use is a Grouped report

* Name and Type are both reserved words and shouldn't be used as fieldnames
Problem names and reserved words in Access by Allen Browne
http://allenbrowne.com/AppIssueBadWord.html

 

[strive4peace]

ps, gmatriix
you can also put names in a subreport so they are in columns like this: