Click here to Skip to main content
12,305,151 members (73,709 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: .NET3.0 C# .NET Access
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 22-Apr-13 1:46am
Edited 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

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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
  Permalink  
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)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160530.1 | Last Updated 24 Apr 2013
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100