Crosstab with multiple character fields

[ecology]

I have data on individuals that may be in one of four areas (A, B, C, D, say) in any given year.

George 2000 A
George 2000 B
John 2000 D
Paul 2000 C
Paul 2001 C
Ringo 2001 A
Ringo 2001 B
Ringo 2001 D

I wish to have a crosstab query that has individuals as rows, years as columns, and the area(s) seen as value.

2000 2001
George AB
John D
Paul C C


Ringo ABD

If I select "first" I get the area it was seen during the first record for the year. If I select "last" I get the final area. However, I wish to see all of them, up to four. Can anyone help on how to accomplish this?

[June7]

Cannot do a conventional crosstab query with that data as is for the output you want. If you want the areas combined into a single field for each individual for each year, use VBA code. Review: http://allenbrowne.com/func-concat.html

Whether or not a crosstab can be based on the resulting output of the VBA code is not something I've ever tried. This VBA procedure is known to perform slowly with large datasets.

[ecology]

That looks very promising. Thanks.

How large? I have about 20,000 records, though this particular analysis is not one that I need to do frequently.

[June7]

That should not be too large for the function. I don't know if crosstab based on the first query using that function will choke.

[ecology]

Just to let you know. It worked a charm. I set it up in two steps. I used your code to concatenate, then a crosstab to lay out the results in a more friendly format.

The only thing I struggled with a bit was the syntax for the third argument - the Where clause for filtering values. Got is sorted though. Again, many thanks.

[ecology]

I have a quick followup. I will only be doing this concatenation periodically, but I will then be using the output from it a lot in other queries and reports. Because using the calculated field can make things work slowly (the initial crosstab using it takes ages, and seems to go through the same slow process every time I use it), I would like to save the output from the calculated field as values rather than expressions. My first approach was to try making this a make-table query and see if the new table had these as calculated or as values. However, when I did this I got the error message "A calculated column cannot be saved without a valid expression in the Expression property".

I am not sure how best to convert the results of the expression into text.

[June7]

Major modification of Allen Browne code.

Instead of calling the function from a query, have code open a recordset of the raw data, loop through the recordset and build string of the related values and write record to table. The loop repeats when some value in recordset changes, such as customerID or whatever is used to define the group.

Code could be in a button Click event.

[ecology]

You lost me completely. Is it really that hard to convert the output of an expression to text in Access?

I suppose I will just export it to Excel and re-import it.

[June7]

The output from Allen Browne's function is already text. I don't really know why the MAKE TABLE action has an issue with it.

[ecology]

Final piece that got it working properly. I hope this may be helpful to others.

I know that in some code, the use of table or field names with spaces can cause problems. I had one of those in my relationship. I renamed so that there was nothing that included a space. I initially had it square-bracketed, which the ConcatRelated() function instructions indicated would be adequate. It worked well enough to display in a select query, but gave an error message in make-table, and dragged every other use of the output from that query to a grinding halt.

Without the space and square brackets it works just as one might wish.