String details out horizontally rather than vertically

[Rakier]

I was wondering if there was a way to have the details listed in a horizontal list rather than vertically.

Example: I have an item that can be ordered by more than one user. Currently I can get it to do this:

Item........Catalog #...........User

Widget..............12345............................Joan
...............................................................Kevin
...............................................................Anita


What I want to do is:

Widget..............12345...........................Joan, Kevin, Anita

Is there any way to do this?

 

[RichMorrison]

See Help for "crosstab query"

RichM

 

[Rakier]

Rich,

Thanks for the input. I can understand the crosstable query, but is there a way to get this to print in this manner on a report.

Basically, what I have is an item with three different users. Everything prints fine in the detail section except that I'd like to get the three users to appear in a horizontal list, rather than a vertical list.

Any ideas?

 

[RichMorrison]

I have used crosstab queries in reports a few times but it has been a while. It is tricky. The problem is the "Column Heading" output of the query. You won't necessarily know a consistent field name to use on a report.

I have made trend reports where I generated a sequence and used that field as column heading. That produced output named 01, 02, etc. Then I could use the fieldname as a control source on a report textbox.

Good luck chief,
RichM

 

[Rakier]

Rich,

Thanks again for the input. I'll give it a whirl.

 

[John Routledge]

Hi Rakier

I am having similar problem. If the crosstab solution
works for you please send me details.

thanks, John

 

[John Routledge]

Hi

Crosstab solution works, here is example

TRANSFORM First(Members.FirstName) AS FirstOfFirstName
SELECT Family.FamilyName, Family.[Home Phone]
FROM Family LEFT JOIN Members ON Family.FamilyID = Members.FamilyID
WHERE (((Family.[Postal/Zip])="V9R 6R1"))
GROUP BY Family.FamilyName, Family.[Home Phone], Family.[Postal/Zip]
ORDER BY Family.FamilyName, Members.RoleID
PIVOT Members.RoleID In (1,2,3,4,5,6);

John