Cross Tab Query - data from columns to rows 3

[jpkeller55]

Appreciate any help with the following:

I have a table (FaxNum) with the following data:

LAST FIRST Fax
Smith John 666-333-2222
Jones Terry 666-333-2222
House Ray 888-999-1111
Blake Rob 888-999-1111
Johnson Sam 888-999-1111
White Barry 888-999-1111
Rio Jose 888-999-1111

I would like to run a query to return:

Fax Name1 Name2 Name3 Name4 Name5 Name6 Name7
203-737-5453 Jones, Terry Smith, John
205-975-9320 Blake, Rob House, Ray Johnson, Sam Rio, Jose White, Barry

Thanks!

 

[jpkeller55]

Sorry, disregard my first post...this is what I meant to post.

Appreciate any help with the following:

I have a table (FaxNum) with the following data:

LAST FIRST Fax
Smith John 666-333-2222
Jones Terry 666-333-2222
House Ray 888-999-1111
Blake Rob 888-999-1111
Johnson Sam 888-999-1111
White Barry 888-999-1111
Rio Jose 888-999-1111

I would like to run a query to return:

Fax Name1 Name2 Name3 Name4 Name5 Name6 Name7
666-333-2222 Jones, Terry Smith, John
888-999-1111 Blake, Rob House, Ray Johnson, Sam Rio, Jose White, Barry

Thanks!

 

[Remou]

I don't think you can do that with a crosstab. Would concatenating the fields suit?

http://www.www.tek-tips.com/faqs.cfm?fid=4233

 

[jpkeller55]

I could easily concatentate the First and Last name in the table. So, really all I would need to figure out is how to get the data into rows. for example I would have the following data to start in the table
Name Fax
Smith, John 666-333-2222
Jones, Terry 666-333-2222
House, Ray 888-999-1111
Blake, Rob 888-999-1111
Johnson, Sam 888-999-1111
White, Barry 888-999-1111
Rio, Jose 888-999-1111

Then would need it converted to:
Fax Name1 Name2 Name3 Name4 Name5 Name6 Name7
666-333-2222 Jones, Terry Smith, John
888-999-1111 Blake, Rob House, Ray Johnson, Sam Rio, Jose White, Barry

 

[lespaul]

remou was asking if you needed the Name1, Name2 headers. There's a function in FAQ701-4233 that will easily give you something like this:
[tt]
Fax Name
666-333-2222 Jones, Terry; Smith, John
888-999-1111 Blake, Rob; House, Ray; Johnson, Sam; Rio, Jose; White, Barry[/tt]

Leslie

Have you met Hardy Heron?

 

[jpkeller55]

les, I really do not need the headers, just the data grouped by fax number in rows as you have illustrated. I will see if I can figure out 4233.

thanks for your help.

 

[dhookom]

If you really want numbered columns, consider this solution which uses the employee table from Northwind.
The first query (qselRankTitle) creates a RankOrder column by Title for each employee:

SELECT Employees.Title, Employees.LastName, Count(Employees_1.EmployeeID) AS RankOrder
FROM Employees LEFT JOIN Employees AS Employees_1 ON Employees.Title = Employees_1.Title
WHERE (((Employees.EmployeeID)<=[Employees_1].[EmployeeID]))
GROUP BY Employees.Title, Employees.LastName
ORDER BY Employees.Title;

The final query creates the crosstab:

TRANSFORM First(qselRankTitle.LastName) AS FirstOfLastName
SELECT qselRankTitle.Title
FROM qselRankTitle
GROUP BY qselRankTitle.Title
PIVOT qselRanktTitle.RankOrder;

[tt][blue]
Title 1 2 3 4 5 6
Inside Sales Coordinator Callahan
Sales Manager Buchanan
Sales Representative Dodsworth King Suyama Peacock Leverling Davolio
Vice President, Sales Fuller[/blue][/tt]

Duane
Hook'D on Access
MS Access MVP

 

[lespaul]

wow Duane, you're my hero!

Les

 

[dhookom]

I [red]borrowed[/red] the first query from fellow MS Access MVP John Spencer. I am sure PH has also posted similar SQL.

Duane
Hook'D on Access
MS Access MVP

 

[Remou]

Very honest as well as helpful!

 

[dhookom]

I originally attempted to use a subquery within the crosstab to create the RankOrder but found crosstabs don't allow this. My less efficient solution was to use DCount() in the crosstab to replace the subquery. While this worked, it was not as efficient as the current solution using two queries.

Duane
Hook'D on Access
MS Access MVP