Crosstab Query / Report

[dazoloko via AccessMonster.com]

Hi all

Ive searched through a number of post but cant seem to find the answer to
what Im looking for so I guess Im best off asking !

I have a table which contains service user details, two of the fields being
User Group and Service Type
The service can also be current or ended.

I have a select query which picks out all of the current services.

I then have a crosstab query which summarises this to look like something
like below ;-


Service Type
User Group Service Type 1 Service Type 2 Service Type 3
User Group 1 10 10
1
User Group 2 9 8
User Group 3 11 4


I then have a report linked to the crosstab query

This works fine until the Service Type 3 is no longer has any 'Current'
Service Users. The query dosent include that service type and therefore the
report dosent recognise it.

Is there a way around this ?

Cheers

Darren

 

[KARL DEWEY]

Save a copy of the crosstab query for safety reasons.
Open orginal query in design view. Click on VIEW - SQL View.
Last line will read PIVOT .....; The dots represent the field and any
formating.
Just before the semicolon type in this --
IN ("Service Type 1", "Service Type 2", "Service Type 3")
using the actual column names produced by the crosstab
query.

Then the last line should look like this --
PIVOT .... IN ("Service Type 1", "Service Type 2", "Service Type 3");

 

[Marshall Barton]

Ive searched through a number of post but cant seem to find the answer to
what Im looking for so I guess Im best off asking !

I have a table which contains service user details, two of the fields being
User Group and Service Type
The service can also be current or ended.

I have a select query which picks out all of the current services.

I then have a crosstab query which summarises this to look like something
like below ;-


Service Type
User Group Service Type 1 Service Type 2 Service Type 3
User Group 1 10 10
1
User Group 2 9 8
User Group 3 11 4


I then have a report linked to the crosstab query

This works fine until the Service Type 3 is no longer has any 'Current'
Service Users. The query dosent include that service type and therefore the
report dosent recognise it.

If the service types are unchanging, then set the crosstab
query's ColumnHeadings property to the list of service types
used in the report.

 

[dazoloko via AccessMonster.com]

Thank you both for your suggestions, I shall give them a whirl.

Thanks

D

Save a copy of the crosstab query for safety reasons.
Open orginal query in design view. Click on VIEW - SQL View.
Last line will read PIVOT .....; The dots represent the field and any
formating.
Just before the semicolon type in this --
IN ("Service Type 1", "Service Type 2", "Service Type 3")
using the actual column names produced by the crosstab
query.

Then the last line should look like this --
PIVOT .... IN ("Service Type 1", "Service Type 2", "Service Type 3");

Hi all

[quoted text clipped - 28 lines]

Darren

 

[Marshall Barton]

They are the same suggestion. One is how to do it in design
view and the other in SQL view.

 

[dazoloko via AccessMonster.com]

Thanks, it worked great.

D

Ive searched through a number of post but cant seem to find the answer to
what Im looking for so I guess Im best off asking !

[quoted text clipped - 20 lines]

Service Users. The query dosent include that service type and therefore the
report dosent recognise it.

If the service types are unchanging, then set the crosstab
query's ColumnHeadings property to the list of service types
used in the report.