Concatenating Record Values In Database (ms Access) Or In Server Side Code (asp.net)

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

Solution 1:

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
GROUPBY
    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

Solution 2:

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

Sample:

SELECT RecNo, Last(fConcat([RecNo],[Field5])) AS Field5 FROM myTable GROUPBY 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

EndFunction

The solution works very fast

Solution 3:

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.

Solution 4:

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 .