Crosstab query?

[Jessica]

Hi,

I have a database that keeps track of the varios committees that each person
is on each year. Now I need to build a table with people's name as row
heading, year as column heading and list of committees as value (see sample
below). I tried using a crosstab query but doesn't work.

2000 2001
2002
Amy Social, Seminar Research, Search Safety, Social
Peter Safety Search
Research, Safety, Seminar

Any assistance would be greatly appreciated!

Jessica

 

[Tom Ellison]

Dear Jessica:

This sort of requirement comes up from time to time. Yours is a fairly
nasty example.

I put together a table with 3 columns and placed the data in it from your
example. Then I coded the following:

SELECT AName, AYear,
MIN(Committee) &
IIf(IsNull((SELECT Committee
FROM SomeTable T1
WHERE T1.AName = T.AName
AND T1.AYear = T.AYear
AND (SELECT COUNT(*)
FROM SomeTable T2
WHERE T2.AName = T1.AName
AND T2.AYear = T1.AYear
AND T2.Committee < T1.Committee) = 1))
, "", ", " &
(SELECT Committee
FROM SomeTable T1
WHERE T1.AName = T.AName
AND T1.AYear = T.AYear
AND (SELECT COUNT(*)
FROM SomeTable T2
WHERE T2.AName = T1.AName
AND T2.AYear = T1.AYear
AND T2.Committee < T1.Committee) = 1))
&
IIf(IsNull((SELECT Committee
FROM SomeTable T1
WHERE T1.AName = T.AName
AND T1.AYear = T.AYear
AND (SELECT COUNT(*)
FROM SomeTable T2
WHERE T2.AName = T1.AName
AND T2.AYear = T1.AYear
AND T2.Committee < T1.Committee) = 2))
, "", ", " &
(SELECT Committee
FROM SomeTable T1
WHERE T1.AName = T.AName
AND T1.AYear = T.AYear
AND (SELECT COUNT(*)
FROM SomeTable T2
WHERE T2.AName = T1.AName
AND T2.AYear = T1.AYear
AND T2.Committee < T1.Committee) = 2))
AS NameList
FROM SomeTable T
GROUP BY AName, AYear

This is NOT a crosstab, but you should be able to make a crosstab out of it.
It produced the following results:

AName AYear NameList
Amy 2000 Seminar, Social
Amy 2001 Research, Search
Amy 2002 Safety, Social
Peter 2000 Safety
Peter 2001 Search
Peter 2002 Research, Safety, Seminar


The table with the data I called "SomeTable" with columns AName, AYear, and
Committee.

You would need to change the name of the table and of the columns to
whatever you have.

Making a comma separated list in a single column is an interesting task.
The code I gave will work only up to 3 committees per person per year. I
could be extended for a larger number, however.

Tom Ellison

 

[Duane Hookom]

You can create a standard crosstab query using the generic concatenate()
function found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane as the
Value. The concatenate function can return all the committees base on the
person and year.

 

[Jessica]

Hi,

I've now got each person's yearly committee membership concatenated using
Hookom's function. While working on the crosstab query. I do not know how to
make it list the membership. The options are count, average, product etc.
Which option should I choose to make it just list the value instead of doing
any mathematical calculations?

Thanks in advance for your kind assistance!

Jessica

 

[Duane Hookom]

Try First.
--
Duane Hookom
MS Access MVP

Hi,

I've now got each person's yearly committee membership concatenated using
Hookom's function. While working on the crosstab query. I do not know how
to
make it list the membership. The options are count, average, product etc.
Which option should I choose to make it just list the value instead of
doing
any mathematical calculations?

Thanks in advance for your kind assistance!

Jessica