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

Question

Hi,

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

Answer 1

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.

Answer 2

Thank you for your answer but that's too advanced for me. I didn't get that at all. Is there a simpler solution to this? I can dismiss the Access database and try something else if it's going to work.

Answer 3

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 .

Answer 4

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

Answer 5

Thank you very much Russ Cam! That's exactly what I need. I'm having some trouble executing the query. I keep getting error "3061 - too few parameters, expected 1" but I'll get around it. Thank you!

Answer 6

I want to but I can't. I don't see any checkmarks and I'm not allowed to vote due to low points.

Answer 7

That was before I registered here. I used to ask questions unregistered, now I got my OpenID with the same username and now that account doesn't work. I can't login back as donetasp35 as unregistered profile because it won't accept me. I used that account to ask the question and I registered in the meanwhile. I apologize for this. I'll try to work something out to give you credit but if I don't, I'm really sorry.

EDIT: I need 15 reputation to vote so I can always come back here and give you a vote. Also, I'm sorry to waste everybody's time with this. I didn't know I can't call VBA modules from outside such as ASP.NET environment so I can't use Russ's solution. Thank you all for your help.