Show text in crosstab value

[Guest]

Is it possible to show text instead of numbers in a crosstab query result? I
have a table that has three fields, all text. I want to use one of the
fields as the row header, another field as the column header, and the third
field as the value in the crosstab based on the first two fields it's
associated with. Can this be done? From what I see, only numbers can be in
the value field of a crosstab query. Thanks for your help.

 

[Allen Browne]

Crosstabs are usually based on number fields, where you sum or count values.
However they can generate text.

Presumably there is only one text value possible for any point in the
matrix. You can therefore use First in the Total row on the field used for
Value, so that Access can grab the first match (the only one as it happens)
and show it at the matrix point.

 

[Guest]

Thanks Allen, but they way I want to orient the crosstab results, this omits
data. For instance, the table that the query will be based on looks like
this:

JOB TITLE Assigned to Job Type
Title 1 District 1 Sales
Title 2 District 2 Service
Title 3 District 2 Service
Title 4 District 1 Sales
Title 5 District 1 Service
Title 6 District 2 Service
Title 7 District 2 Service
Title 8 District 2 Sales

And I want the crosstab results to look like this:


Job Type
Assigned to Sales Service
District 1 Title 1
Title 4 Title 5

District 2 Title 8 Title 2
Title 3
Title 6
Title 7

Using "First" as the Query total shows only the first title for that job
type and omits all the others.
Is this possible to do with a crosstab query or do you know of a better way
to do this? A Pivot table kind of works, but I just want to print it like a
report without all of the pivot table row and column headers showing. Thanks
for any help you can provide.

 

[Allen Browne]

If First does not do the job, then you have multiple values you are wanting
to display at the intersection point. AFAIK, a crosstab cannot concatenate
text values like that.

 

[Guest]

Thanks, any other ideas on ways to show the results this way other than using
a crosstab query?

 

[George Nicholson]

You can do something like this but not as a crosstab. It's sort of a PIB
(and as attractive as you might like), but doable in general.

Create separate Sales and Service fields in your query and use iif()s to
populate them. Something like the following (this is aircode)
SELECT AssignedTo, iif([JobType]="Sales", [JobTitle],"") As Sales,
iif([JobType]="Service", [JobTitle],"") As Service FROM MyTable

Note that because of the way your data is structured (which is fine, AFAIK)
you'll never get both a Sales and Service value on the same line.

HTH,

 

[Guest]

Great Idea, I can make it work that way. Just need to be careful when new
jobs are added or removed. Thanks.
--
Steve Krieger

You can do something like this but not as a crosstab. It's sort of a PIB
(and as attractive as you might like), but doable in general.

Create separate Sales and Service fields in your query and use iif()s to
populate them. Something like the following (this is aircode)
SELECT AssignedTo, iif([JobType]="Sales", [JobTitle],"") As Sales,
iif([JobType]="Service", [JobTitle],"") As Service FROM MyTable

Note that because of the way your data is structured (which is fine, AFAIK)
you'll never get both a Sales and Service value on the same line.

HTH,
--
George Nicholson

Remove 'Junk' from return address.