Click here to Skip to main content
15,868,340 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have a Table
QID     Qtext   QType           SeqNo   Header  QuestionGroupedTo
31	sdsad	Group Type	31	sdsd	30
32	dsd	Group Type	32	sd	30
43	ssd	Group Type	40	sd	39
44	ssd2	Group Type	41	ssd2m	39
45      dsds    Group Type      43      asdasd  39


i want the table structure to be
QID      QuestionGroupTo
31~32     30  
43~44~45  39


How can i achieve this
Posted
Updated 22-Apr-13 1:59am
v2
Comments
Sandeep Mewara 22-Apr-13 7:52am    
And what have you tried so far?
gaurish thakkar 22-Apr-13 7:59am    
I tried to use Group concat and



SELECT questions.questionId, questions.questionGroupedTo
FROM (questions INNER JOIN
questions questions_1 ON questions.questionGroupedTo = questions_1.questionId)
WHERE (questions.questionType = 'Group Type')) as t on t.questionType
Foothill 22-Apr-13 11:47am    
What fields are you targeting for the merger and what kind of operations are you wanting to perform on the fields (e.g. sum, count, etc,...).
gaurish thakkar 22-Apr-13 12:13pm    
i just want to the child question which point to same parent question to be concatenated with ~

1 solution

First of all, you must create a module with this code:

Option Compare Database

Public Function Aggregate(value As Integer) As String
    Dim retValue As String
    
    Dim db As Database
        Set db = CurrentDb()
    
    Dim sql As String
    sql = "SELECT * FROM T WHERE QuestionGroupedTo=" & value
    
    Dim rs As Recordset
        Set rs = db.OpenRecordset(sql)
    
    If Not rs.BOF And Not rs.EOF Then
        rs.MoveFirst
        Do While Not rs.EOF
            If retValue <> "" Then retValue = retValue & "~"
            retValue = retValue & CStr(rs("QID"))
            rs.MoveNext
        Loop
    End If
    
    
    Aggregate = retValue
End Function


This is an aggregate function that you create to concatenate the required values.

And then your sql sentence can be like this:

SELECT T.QuestionGroupedTo, Aggregate([QuestionGroupedTo]) AS Expr1
FROM T
GROUP BY T.QuestionGroupedTo, Aggregate([QuestionGroupedTo]);


greetings
 
Share this answer
 
v2
Comments
gaurish thakkar 25-Apr-13 7:05am    
TRANSFORM First( s.questionId) AS FirstOfCHICKTIME SELECT s.[questionGroupedTo] from ( SELECT questions.questionId, questions.questionGroupedTo FROM (questions INNER JOIN questions questions_1 ON questions.questionGroupedTo = questions_1.questionId) WHERE (questions.questionType = 'Group Type')) as s GROUP BY s.questionGroupedTo PIVOT s.questionId; ", con);
Thregarth 25-Apr-13 18:03pm    
ok, but it does not indicate the version of access, I have not assumed that version was, and I made ​​the most general solution, either it seems good.
gaurish thakkar 30-Apr-13 1:41am    
Thank you for the reply :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900