group some values as "Other", display remaining in same field 1

[liblan]

Hi
In Access 2003 I have:
T.ParticipantTypes T.ParticipantNumber
Adults 123
Teens 456
Children 789
Seniors 0
Families 123
Daycare 456

I created a crosstab query that shows:
Adults Teens Children Seniors Familes Daycare
Total 258 123 0 0 0 123 12
1701 0 456 789 0 0 456

I need the crosstab query to show:
Adults Children Others
Total 258 123 0 135
1701 0 789 912
My Crosstab Query SQL goes like this:

TRANSFORM Sum(CrossTabBase.ParticipantNumber) AS SumOfParticipantNumber
SELECT CrossTabBase.LocationType, CrossTabBase.SponsorType, Sum(CrossTabBase.ParticipantNumber) AS [Total Of ParticipantNumber]
FROM CrossTabBase
GROUP BY CrossTabBase.LocationType, CrossTabBase.SponsorType
ORDER BY CrossTabBase.SponsorType
PIVOT CrossTabBase.ParticipantType;

CrosstabBase query goes like this:

SELECT CentralTable.ProgramDate, CentralTable.ParticipantNumber, LocationType.LocationType, ParticipantType.ParticipantType, SponsorType.SponsorType
FROM SponsorType INNER JOIN (ParticipantType INNER JOIN (LocationType INNER JOIN CentralTable ON LocationType.LocationID=CentralTable.LocationID) ON ParticipantType.ParticipantID=CentralTable.ParticipantID) ON SponsorType.SponsorID=CentralTable.SponsorID;

Any solutions, or perhaps much, much easier way to get my data to display?

Thanks in advance!

 

[dhookom]

Add a [TypeGroup] field to your table of unique participant types. Enter one of these values Adults Children Others into the field so that your crosstab and PIVOT on the new field rather than ParticipantType.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]