Rows into Columns

[Prince]

I want to write a query to show 3 rows record in 1 row
eg:
Emp# Name Amount
002 ABC 2,000
002 ABC 3,000
002 ABC 5,000

I want to print like:
002 AbC 2,000 3,000 5,000

(Max 3 Rows per employee in database)

Please advice if you have some simple solution

 

[Michel Walsh]

You can rank your record and then use a crosstab.

To rank your record (I assume you have a primary key field, pk):

SELECT empNumber, Name, amount,
(SELECT COUNT(*) FROM tableName As b
WHERE b.empNumber=a.empNumber
AND (a.amount < b.amount
OR ( a.amount=b.amount AND a.pk <=b.pk
) ) ) AS rank
FROM tableName As a


Save the query, say under the name q1, then use the crosstab:


TRANSFORM LAST(amount)
SELECT empNumber, Name
FROM q1
GROUP BY empNumber, Name
PIVOT rank



Note that the rank uses the amount value to 'order' them, within the same
empNumber. Maybe you prefer to just use the primary key, to find the rank,
and thus, use a join:


SELECT a.pk, LAST(a.empNumber), LAST(a.Name), LAST(a.amount), COUNT(*) AS
rank
FROM tableName AS a INNER JOIN tableName AS b
ON a.empNumber = b.empNumber
AND a.pk <= b.pk
GROUP BY a.pk


for your first query. (probably a little bit faster than using the
subquery).



Vanderghast, Access MVP

 

[KARL DEWEY]

I do not think that the crosstab query will recognize the q1 fields and
therefore need to use a temporary table for the data.

 

[Ken Sheridan]

As you say to want to 'print' the result you might like to consider using a
report with a subreport rather than a crosstab query.

1. Base the main report on a query on the Employees table (assuming you
have one), returning the Emp# and Name columns

2. Base the subreport on a query similar to your current query, but
returning only the Emp# and Amount columns.

3. Link the main and subreports on Emp#.

4. Set up the subreport as a 3-column layouit with 'across then down'
columns.

5. Position the subreport immediately to the right of the Emp# and Name
columns in the main report.

You'll find an example of this at:


http://community.netscape.com/n/pfx...libraryMessages&webtag=ws-msdevapps&tid=24271


I originally produced the file in response to a question by a reader of a
magazine column written by a contact of mine; it lists people (club members)
by address in a horizontal layout similar to that which you want for the
amounts.

The file also includes a single report solution in which the layout is
amended in code at runtime. This was intended to show that this could be
done, but I'd not recommend it as the subreport solution is far simpler, and
the end result is identical in each case.

Ken Sheridan
Stafford, England

 

[Michel Walsh]

The following works in Northwind (Access 2003):



SELECT LAST(a.ContactTitle) AS title,
LAST(a.CompanyName) AS cname,
COUNT(*) AS rank
FROM Customers AS a INNER JOIN Customers AS b
ON (a.CustomerID >= b.CustomerID)
AND (a.ContactTitle=b.ContactTitle)
GROUP BY a.CustomerID



for q1 and, for the crosstab:



TRANSFORM Last(q1.cname)
SELECT q1.title
FROM q1
GROUP BY q1.title
PIVOT q1.rank



without using temporary table. Maybe a problem with the Access version you
are using? (although I am unaware of such problem)



Vanderghast, Access MVP