Combining record values ​​in a database (MS Access) or server-side (ASP.NET)

Question

sorry to bother you people again. I searched all over the Internet, but I can’t find a solution to my problem. I have two tables in Access, and the output is as follows:

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

I want this to be:

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

How can i do this? He tried to play with SQL commands in the Access database, but nothing worked. I also looked for a solution using GridView in ASP.NET, but no luck. Is there a way to do this or should I use a different approach?

The best I can do is GROUP BY, so the output is as follows:

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

Answer 1

I believe that you are looking for something like this -

Attach a collection of objects to a comma separated string

I would be inclined to pull the data the same way you did GROUP BY in you in a question, and then combine the values ​​of the numbers for each object in your asp.net code.

Or you could write a VBA function to use in Access for this. Allen Browne is already written here . You just need to add the code to the module inside Access, and then you can use this function in SQL queries in 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 

Next SQL with VBA Function

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

gives the following result:

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

if you want to receive the order that you indicated in your question, you will need another field / expression on which ordering will be performed

Answer 2

There is a simple solution. Use the aggregated function last() in your query.

Example:

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

The Last () function calls the Concat function for each record to be grouped. Keep in mind to use global variables that are defined outside the Concat function.

Below is a simple code as 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 is very fast.

Answer 3

If I had MS Access and ASP.Net to solve this problem, I would take the route of nested repeaters.

So, I would make two calls in the database, first call to get separate categories (in your case - IDSUBJECTS), the second call is to get all GRADE fields for all IDSUBJECTS. If you do this by username, I would add a username to each of these calls.

Then I linked one repeater to separate IDSUBJECTS, placed the Literal control and the repeater in the itemtemplate . Then I get the text from the literal control and the link to the internal relay in the Repeater_ItemDataBound event and filter the second table based on IDSUBJECT in the external relay. In the internal repeater, iterate over the filtered data and display as you want.

Not the easiest solution, but it can work.

If you have SQL Server (you can download Microsoft SQL Server Express for free), you might want to check out the PIVOT function.

Answer 4

A very interesting way to handle this if Microsoft SQL Server is an option is to use Common Table Expression . Using CTE will allow you to combine a class column into one cell by class. Simple-Talk has a very nice walkthrough that explains the different approaches to SQL concatenation and provides examples of using CTE (look for WITH statements).

If you do not have Microsoft SQL Server, download Microsoft SQL Server 2008 Express .