Concatenate text string in crosstab query Access 2003

Hello experts,

I'm using a crosstab query in Access 2003 using a TRANSFORM and PIVOT

Field name:       qryWeightedTraits.tblTrait.name
From:                qryWeightedTraits1

As its a crosstab with multiple records I think I need an aggregate function, but I'm trying to get a text string of each of the records one record after the other i.e. the text of each record in a single string -  record1 & record2 & record3 .. ect.

So far I've used

(count() & first() & last()) As tblTrait_name

Which gives me the number of records, and text for two records, but I've got groups of records upto 4.

Is there some sort of 'concatenate' I can use in this situation that would give me the text from each record one after the other as a single string?

In the below code I've got this running in an iif()  
TRANSFORM Sum(qryWeightedTraits1.weightedTotal) AS SumOfweightedTotal
SELECT qryWeightedTraits1.speciesID, qryWeightedTraits1.SpeciesName, First(qryWeightedTraits.traitScoreID) AS traitScoreID, first(qryWeightedTraits.traitID) AS traitID, first( qryWeightedTraits.charID) AS charID, first(qryWeightedTraits.tblCharacter.name) AS tblCharacter_name, iif(count(qryWeightedTraits.tblTrait.name)=1, first(qryWeightedTraits.tblTrait.name), count(qryWeightedTraits.tblTrait.name) & first(qryWeightedTraits.tblTrait.name) & last(qryWeightedTraits.tblTrait.name)) AS tblTrait_name, first(qryWeightedTraits.value1) AS value1, sum(qryWeightedTraits.traitWeight) AS traitWeight, first(qryWeightedTraits.charWeight) AS charWeight, first(qryWeightedTraits.complexID) AS complexID, first(qryWeightedTraits.complexName) AS complexName
FROM qryWeightedTraits1
GROUP BY qryWeightedTraits1.speciesID, qryWeightedTraits1.SpeciesName
ORDER BY qryWeightedTraits1.SpeciesName
PIVOT qryWeightedTraits1.value1;

Open in new window

LVL 1
davecocksAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
see this sample db..
the function concatTrait is in module1

run query1 to see the result
sampleConcatDB.mdb
0
 
Rey Obrero (Capricorn1)Commented:
you will need a user define function to do that..
post sample data from your crosstab query, and the result you want to see
0
 
davecocksAuthor Commented:
Legend! sounds exciting.

I hope xls files are OK. I can do txt or whatever, if you need it.

The 'orginal Data' file contains the equivalent of qryWeightedTraits1 from the code I posted.

The 'result' is how I hope it will look. If you need anything else just let me know. I can live without the count() of cells in the tblTrait_name field if its tricky.

Cheers

Original-Data.xls
Result.xls
0
 
davecocksAuthor Commented:
Fantastic!! Another great solution. Many thanks!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.