Converting Row data to Modified Columnar format

[adventurous1]

I need help converting the original row data below to the reformatted output.

All help is appreciated!

Original Row Data

PARENT_ISSUE LINKED_ISSUE STATUS
179 194 Closed
179 189 Closed
205 116 Closed
62 357 Pending
62 363 Pending

Reformatted Output

PARENT_ISSUE STATUS_CLOSED STATUS_PENDING
179 189, 194
205 116
62 357, 363

 

[dhookom]

First, use the function at faq701-4233 to create the comma delimited string. Then use the result in a crosstab query that has Parent_Issue as the Row Heading and Status as the Column Heading. Use First of the concatenated string as the value.

Duane
Hook'D on Access
MS Access MVP

 

[adventurous1]

Duane,

Thanks... for the most part this worked but when I ran the cross tab query it gave me an error message that there were too many records. Is there another way to run this to easily (in a scaled manner) to send this directly to a table?

Thanks!

 

[dhookom]

I have never heard of an error message of too many records. I have seen error messages regarding too many fields.

Can you share your crosstab SQL view? I think you did something wrong but can't tell.

Duane
Hook'D on Access
MS Access MVP

 

[adventurous1]

Duane,

The cross tab is setup as:

Parent Issue > Row Heading
Status > Column Heading
Linked Issue > Value (Total = First)

The error message I receive is: Record is too large.

Any thoughts?

Thanks!

 

[dhookom]

That's not the SQL view.
How about providing more information about your records?

Duane
Hook'D on Access
MS Access MVP

 

[adventurous1]

Sample of records in first post. There are about 142 different Status types. Some of the Parent Issues can have many, many Linked Issues. Is there a limit somewhere that I should be aware of?

 

[dhookom]

142 different Status types" you've got to be kidding ;-)

The "sample of records in first post" is not nearly as significant as the number of types and linked issues.

There is a limit to the number of characters in a record/row. I think it's 2k or close.

You may need to filter your records or create groups of status types for different crosstabs.

Duane
Hook'D on Access
MS Access MVP

 

[adventurous1]

ah, did not know about the 2k limit. bummer as with the exponential increase in data these days, life is not always that simple. Agree that the # of statuses is insane, but it's not my design... Thanks!

 

[dhookom]

As I suggested, consider group and/or filtering the data down to manageable columns, values, records.

Duane
Hook'D on Access
MS Access MVP