Cross Tab Line Numbers

[Guest]

I have two fields, Emp_ID and Proj. Each employee could have as many as 10
projects at any given time. What I want to do is build a CrossTab Query that
has the emp_id as column headers and under each name the list of projects
assigned to them. So what I was trying to do is to create a linenumber of
some sort between 1 and 10 to go with each record so that when I pull the
Query into the Cross tab query I could use the number as row headers. Pleae
help!

Example of what I want limit to 5 to save space (not 10):

no. Emp1 Emp2 Emp3 .....
1 proj1 proj2 proj8
2 proj3 proj5 proj6
3 proj4 proj9
4 proj7
5

 

[Shaun]

As far as the column headings go, you can use a Select query grouping by
Emp_ID and counting Projects. Use the results of this query as your column
headings ex. "Proj" & [CountofProj].

I'm still trying to figure out the rest of it though (similar problem here).
I can only make it give me the same number across all the columns (whether
that's a sum, count, first, etc). If you know how to do the rest, let me
know.

 

[John Spencer]

Assumptions:
-- There are cases where there are two records with the same EmpId and
ProjId
-- There are less than 250 employees

Base Query: qRankedProjects
SELECT EmpID, Proj
, (SELECT Count(Proj)
FROM SomeTable as S2
WHERE S2.EmpId = S.EmpID
AND S2.Proj < S.Proj) +1 as Rank
FROM SomeTable as S

Crosstab query: Use above query as the source of the crosstab
TRANSFORM First(Proj) as TheProj
SELECT Rank
FROM qRankedProjects
GROUP BY Rank
PIVOT EmpID

Post back if you cannot do this with the SQL view of the query and need help
to use the Design view.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[John Spencer]

FORGOT to limit the records returned to 5

Crosstab query: Use above query as the source of the crosstab
TRANSFORM First(Proj) as TheProj
SELECT Rank
FROM qRankedProjects
WHERE Rank <= 5
GROUP BY Rank
PIVOT EmpID


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..