Concatenating record values in database (MS Access) or in server side code (ASP.NET)

Question

sorry to bother you people again. I've searched all over the internet but I can't find the solution to my problem. I have two tables in Access and the output is like this:

MATH 5
ENGLISH 3
ENGLISH 2
PHYSICS 5
MATH 1
MATH 3

I want it to be:

MATH 5, 1, 3
ENGLISH 3, 2
PHYSICS 5

How can I accomplish this? It tried playing with SQL commands in Access database but nothing works. I also looked for solution using the GridView in ASP.NET but no luck either. Is there a way to acomplish this or should I take a different approach?

Best I can do is GROUP BY so the output looks like this:

MATH 5
MATH 3
MATH 1
PHYSICS 3
PHYSICS 1
...

Can you please provide the following info: 1. Access table structure 2. What tools are you comfortable using (I understand you have MS Access, but can you also use ASP.Net? Is this where the notion of a GridView came from?) — Eugene, Jul 07 '09 at 21:30
Here it is, drawn in Paint for easier comprehension: http://img195.imageshack.us/img195/7352/asdasdnyc.jpg I'm doing this in ASP.NET. I want to accomplish this either by using SQL (multiple queries) or somehow in GridView in ASP.NET. — , Jul 07 '09 at 21:39
Do you want the 5, 1, and 3 in separate columns or do you want to build a comma delimited list with sql? — Joel Coehoorn, Jul 07 '09 at 21:42
No, not separate column. All in one column. I don't have to be comma separated, spaces are good too. Basically all I want it to extract grades from the same subject and put them all together in one field. — , Jul 07 '09 at 21:46

score 2 · Answer 1 · edited May 15 '12 at 21:33

There is an easy solution. Use the last() aggregate function in your query.

Sample:

SELECT RecNo, Last(fConcat([RecNo],[Field5])) AS Field5 FROM myTable GROUP BY RecNo;

The function Last() calls the function Concat for each record that will be grouped. Be aware to use global variables that are defined outside the function Concat.

The following is just a simple code as a proof of concept:

Option Compare Database

Dim glbWert1 As Variant, glbWert2 As Variant

Public Function fConcat(strWert1, strWert2) As Variant

  If strWert1 <> glbWert1 Then
    glbWert1 = strWert1
    glbWert2 = strWert2
  Else
    glbWert2 = glbWert2 + "; " + strWert2
  End If

  fConcat= glbWert2

End Function

The solution works very fast

when using this solution it is very important to order by the identifier (strWert1) **before** doing the query with this function. I was getting errors and traced it to the order the DBMS sent the fields to this function. thanks though. — pgee70, Jan 29 '14 at 03:15

score 2 · Answer 2 · edited May 23 '17 at 12:19

I believe you are looking for something like this -

Join collection of objects into comma-separated string

I would be inclined to pull the data back as you have done in you GROUP BY in the question, and then concatenate the number values for each subject in you asp.net code.

Or you could write a VBA function to use in Access to do it. One has already been written by Allen Browne here. You just need to add the code to a module within Access and then you can use that function within SQL queries within Access.

Given this table structure

subjects table 

id  |  SubjectName  |    Grade
---------------------------------
1      MATH            5
2      ENGLISH         3
3      ENGLISH         2
4      PHYSICS         5
5      MATH            1
6      MATH            3

The following SQL with the VBA function

SELECT 
    subjects.SubjectName + ' ' + 
    ConcatRelated("Grade","subjects","SubjectName='" & SubjectName & "'") AS result
FROM 
    subjects
GROUP BY
    subjects.SubjectName

yields the following result

result
------------
ENGLISH 3, 2
MATH 5, 1, 3
PHYSICS 5

if you want to get the order that you have specified in your question, you will need another field/expression on which to do the ordering

You can't execute an Access VBA function from .NET as the ADO.NET command is executed against the JET db that does not have access to any user defined VBA functions within Access. The only functions that you can use in a SQL command are here - http://support.microsoft.com/default.aspx?scid=kb;en-us;294698&Product=acc under the section "Use Sandbox mode operations with Jet 4.0 Service Pack 3 and later." As recommended, if the OP needs the end result in ASP.NET, I would have a method in the server-side code to do the concatenation on the returned resultset the OP has defined in the question. — Russ Cam, Jul 08 '09 at 07:59

Eugene · Answer 3 · 2009-07-07T22:31:59.200

If I had MS Access and ASP.Net to solve this task, I'd take the route of nested repeaters.

So I'd make two calls to the database, first call to get the distinct categories (in your case -- IDSUBJECTS), the second call is to get all the GRADE fields for all the IDSUBJECTS. If you are doing it by the Username, then I would add Username into each of those calls.

Then I'd tie up the one repeater to the distinct IDSUBJECTS, put a Literal control and a repeater into the itemtemplate. Then I'd get the text from the literal control and a reference to the inner repeater in the Repeater_ItemDataBound event and filter the second table based on the IDSUBJECT in the outer repeater. In the inner repeater, iterate over the filtered data and display in the way you want.

Not the easiest solution, but it can work.

If you have a SQL Server (you can download Microsoft SQL Server Express for free), you may want to look into the PIVOT function.

score -1 · Answer 4 · answered Jul 07 '09 at 22:22

A very interesting way to handle this if Microsoft SQL Server is an option is to use a Common Table Expression query. Using a CTE would allow you to concatenate your grade column into a single cell by class. Simple-Talk has a very nice walkthrough that explains different approaches to SQL contacenation and gives examples of using CTEs (look for the WITH statements).

If you don't have Microsoft SQL Server, download Microsoft SQL Server 2008 Express .

Concatenating record values in database (MS Access) or in server side code (ASP.NET)

4 Answers4

Linked