collapsing rows

[juno]

Hello!

I currently have a select query that returns the following:

lastname|firstname|date|activitytype

When I run the query, I get multiple rows back because a person can be doing more than one activitytype. For example:

kemp|jack|3/26/01|break1
kemp|jack|3/26/01|break2
kemp|jack|3/26/01|lunch

I have collapsed 3 rows into 2 by creating a query that contains two of the same query inside of it. The queries are joined by last name and date. I then added a second activitytype field with the following condition:
[query1].[activitytype] <> [query2].[activitytype]. This yields the following ouput:

kemp|jack|3/26/01|break1|break2
kemp|jack|3/26/01|break1|lunch

How can I change the query so that it produces output that looks like:

kemp|jack|3/26/01|break1|break2|lunch

but will still display a record if it only has one activitytype?

Thanks for any feedback! ;-)

 

[dneufarth]

Try a CrossTab query.


Dave

 

[juno]

A crosstab will not put the data onto a single row.

 

[MichaelRed]

???????? &quot; ... A crosstab will not put the data onto a single row. ...&quot; ????????????

And WHY NOT?? Group by most all, piviot by the Activity?

MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over