Concatenate values and group by sectionID

[tuxalot]

New to Access so I hope you can help point me in the right direction. Trying to get my head around this.

I have data like so:

Question level1 | Question level2 | SectionID | Question text | Response
1 |a | 1 | what color is the sky? | Blue
1 |b | 1 | what do you like about school? | Lunch
2 | 1 | what is your favorite sitcom? | Seinfeld
4 | 2 | Who makes the best hamburgers? | McDonalds
5 | 2 | What is your favorite type of car? | Chevrolet

Recordsource for the report is a crosstab query. Report is grouped on SectionID and I have an unbound textbox "txtResponseAggregate" at the SectionID footer. I would like to show this in txtResponseAggregate:

In the section 1 footer:
1a) Blue; 1b) Lunch; 2 Seinfeld

In the Section 2 footer:
4 McDonalds; 5 Chevrolet

Note: sometimes the SectionID's are not contiguous (depends on data) i.e. might have section 1,2,3,5,10, etc.

I found this http://allenbrowne.com/func-concat.html but I do not see how I can pass the SectionID as a filter. Do I use a sub-report?

Thanks in advance.

[NeoPa]

You may find Listing Items Within GROUP BY Set helpful. It's not an approach I'd generally recommend (as explained within the linked threads) but if you must do it then they explain how.

[tuxalot]

Thanks NeoPa. I will study those solutions and post back if(when) I get stuck.

Cheers.

[tuxalot]

NeoPa - thanks for pointing me to the code referenced. It works fine however it generates a data type mismatch in criteria expression if "Response" if left blank (which sometimes it will be). Any solutions for this?

[patjones]

Hi tuxalot,

You're basically looking to show the answer key for each section's question in the footer for that particular section yes?

Pat

[tuxalot]

zepphead80 - precisely :) Best is if I could have it formatted as per my original post.

[patjones]

Hi,

I'm not entirely sure that using a GROUP BY clause is going to get you anywhere here, because SQL doesn't contain aggregate functions for strings. I did find an article for doing this in T-SQL:T-SQL String Aggregate in SQL Server, which doesn't really help you.

I'm going to think about this a little. I feel somehow that it's possible.

Pat

[patjones]

To clarify my statement in the last post, consider a really simple table with some integer values versus alphanumerical codes:

code value
A1 3
A1 8
B3 2
B3 9
B3 5

You can get the total value for each alphanumerical code by doing this:

SELECT code, SUM(value) AS total_value
FROM tbl
GROUP BY code;

which would produce:

code total_value
A1 11
B3 16

However, as far as I can tell, there is no function such as SUM for strings. Perhaps one of the other contributors can correct me if I'm wrong.

Pat

[tuxalot]

The code is almost there. I added QstnLvl1 & QstnLvl2 as an expression in the underlying query.

One adjustment I would like to figure out is how to order the resulting sql data.

Take this simple example:

SectionID data
01 1
01 2
01 3
03 1
03 2

The SQL datasheet might show:

SectionID Data
01 3,2,1
03 1,2

Is there a way to sort this like
01 1,2,3
03 1,2

I've sorted the underlying query data...does not seem to matter. I think it has something to do with how the function loops.

[patjones]

Hi,

I wasn't sure whether you wanted to delve into VBA or not so I tried to determine a SQL-only solution. I'm glad the VBA function works for you.

If you call the SQL in your previous post "qryConcat", you can reference it in a DLookup call as follows:

txtFooter = DLookup("RspnsComments", "qryConcat", "[SectionID] = '" & Me![txtResponseAggregate] & "'")

RspnsComments is the thing that you want DLookup to pull, so that goes in the first argument. Notice that I wrapped [txtResponseAggregate] in '' signs, because SectionID appears to be a string-valued quantity.

Pat

[NeoPa]

A fair bit to cover here.

@Pat
You are correct as far as there being no such SQL function available to accomplish string aggregation within a GROUP BY. The threads linked to in post #2 though, show how a function can be designed and used to accomplish this.

@Tux
You comment about the function being unable to handle blank (Null) values. This is easy enough to code around once you decide how you want them to be handled. The Nz() function enables you to convert the Null into a string value and the routine can be coded either to ignore these or treat them normally as a value, thus producing a pair of commas (,) in the result.

You also talk about sorting the result set. I think you're missing the point that sorting is done after the SELECTing. I expect if you produce a subquery that is sorted then process that data using the Concat() function, you may get what you require.

[tuxalot]

@Pat
Thanks for the DLOOKUP. This idea worked fine. My final code was:

=DLookUp("[qtestConcatComments]![RspnsComments]","qtestConcatComments","[qtestConcatComments]![SectionID]=" & [SectionID])

@ NeoPa
Adding the Nz() function throws an error - wrong number of arguments. Here's the code:

SELECT qselIndRespWithSurveyName.SectionID, Max(Concat([SectionID],[RspnsComment2], Nz([RspnsComment2], 'Not Found'))) AS RspnsComments
FROM qselIndRespWithSurveyName
GROUP BY qselIndRespWithSurveyName.SectionID;

.

For the sorting, the referenced input query above produces the data sorted as I would hope to see. During the Concat() it seems to be getting a bit mixed up. I've re-built the table referenced by the query above and made sure the data is organized in my preferred order along with the primary key (autonumber). I guess SQL processes data without following any sort order within Access.

[NeoPa]

@Tux
That only happens if you don't follow the instructions correctly.

I'll post the SQL you should have got :

SELECT   [SectionID]
       , Max(Concat([SectionID], Nz([RspnsComment2], 'Not Found'))) AS RspnsComments
FROM     [qselIndRespWithSurveyName]
GROUP BY [SectionID]

On the plus side, using 'Not Found' in place of an empty string is fine.

I'll also delete the post in the other thread as it's not really an addition to the thread and posting the same comment in two threads isn't allowed ;-)

[NeoPa]

Tuxalot:
For the sorting, the referenced input query above produces the data sorted as I would hope to see. During the Concat() it seems to be getting a bit mixed up. I've re-built the table referenced by the query above and made sure the data is organized in my preferred order along with the primary key (autonumber). I guess SQL processes data without following any sort order within Access.

Concat() has nothing to do with it. It processes each record as the data is passed. The mucking-up is done by Access before calling the procedure.

Access sometimes orders incoming data to suit itself. Sorting only really has any effect on data as it's output (Sorry for the bum info earlier). If you're interested in sorting the resultant string you could convert the string into array values (Using Split()) and implement something like the procedure described in Sorting Arrays With QSort.

[tuxalot]

Yes, good I was watching both threads. Sorry about that. And again, thanks NeoPa for the assistance.