Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2008
    Posts
    5

    Unanswered: select query access 2000

    I have a problem. I have a really simple table here:
    table1 with two fields A and B.
    The content of the table is:

    a1, b11
    a1, b12
    a1, b13
    a2, b21
    a2, b22
    a2, b23

    A is primary key.

    My question is: I need to build a query which will get these rows

    a1, b11/nb12b13/n
    a2, b21/nb22/nb23

    from the content of the table1 that I mentioned.
    Would you please suggest some way to build a select which would get these two rows?
    I could think of a query which would look like something like this:

    select A, T1.B,"/n",T2.B
    from table1 as T1, table1 as T2
    where T1.A = T2.A
    but this not only does not solve my problem but it is my closest approach to a possible solution.
    Thanks for your suggestions.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You may be able to adapt this to your needs:

    http://www.mvps.org/access/modules/mdl0004.htm
    Paul

  3. #3
    Join Date
    Jul 2008
    Posts
    5
    Thanks.
    well, sorry, my actual question would be this:
    hello there

    I have a problem. I have a really simple table here:
    table1 with three fields A, B and C.
    The content of the table is:

    a1, b1, c11
    a1, b1, c12
    a1, b1, c13
    a2, b2, c21
    a2, b2, c22
    a2, b2, c23

    A is primary key.

    My question is: I need to build a query which will get these rows

    a1, b1, c11/nc12/nc13
    a2, b2, c21/nc22/nc23

    from the content of the table1 that I mentioned.
    Would you please suggest some way to build a select which would get these two rows?
    I could think of a query which would look like something like this:

    select A, B, T1.C,"/n",T2.C
    from table1 as T1, table1 as T2
    where T1.B = T2.B
    but this not only does not solve my problem but it is my closest approach to a possible solution.
    Thanks for your suggestions.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I would make the same suggestion.
    Paul

  5. #5
    Join Date
    Jul 2008
    Posts
    5
    Quote Originally Posted by pbaldy
    I would make the same suggestion.
    I am lost. I mean: where should I put this code:
    Code:
    ************ Code Start **********
    'This code was originally written by Dev Ashish
    'It is not to be altered or distributed,
    'except as part of an application.
    'You are free to use it in any application,
    'provided the copyright notice is left unchanged.
    '
    'Code Courtesy of
    'Dev Ashish
    '
    Function fConcatChild(strChildTable As String, _
                        strIDName As String, _
                        strFldConcat As String, _
                        strIDType As String, _
                        varIDvalue As Variant) _
                        As String
    'Returns a field from the Many table of a 1:M relationship
    'in a semi-colon separated format.
    '
    'Usage Examples:
    '   ?fConcatChild("Order Details", "OrderID", "Quantity", _
                    "Long", 10255)
    'Where  Order Details = Many side table
    '       OrderID       = Primary Key of One side table
    '       Quantity      = Field name to concatenate
    '       Long          = DataType of Primary Key of One Side Table
    '       10255         = Value on which return concatenated Quantity
    '
    ' Set a reference to DAO
    
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim varConcat As Variant
    Dim strCriteria As String, strSQL As String
        On Error GoTo Err_fConcatChild
        
        varConcat = Null
        Set db = CurrentDb
        strSQL = "Select [" & strFldConcat & "] From [" & strChildTable & "]"
        strSQL = strSQL & " Where "
        
        Select Case strIDType
            Case "String":
                strSQL = strSQL & "[" & strIDName & "] = '" & varIDvalue & "'"
            Case "Long", "Integer", "Double":    'AutoNumber is Type Long
                strSQL = strSQL & "[" & strIDName & "] = " & varIDvalue
            Case Else
                GoTo Err_fConcatChild
        End Select
        
        Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
    
        'Are we sure that 'sub' records exist
        With rs
            If .RecordCount <> 0 Then
                'start concatenating records
                Do While Not rs.EOF
                    varConcat = varConcat & rs(strFldConcat) & ";"
                    .MoveNext
                Loop
            End If
        End With
            
        'That's it... you should have a concatenated string now
        'Just Trim the trailing ;
        fConcatChild = Left(varConcat, Len(varConcat) - 1)
            
    Exit_fConcatChild:
        If Not rs Is Nothing Then
        	rs.Close
        	Set rs = Nothing
        End If
        Set db = Nothing
        Exit Function
        
    Err_fConcatChild:
        Resume Exit_fConcatChild
    End Function
    
    '************ Code End **********
    What I had thought so far was that I should change the query I currently have in the sql view of microsoft access 2000:
    select A,B,C
    FROM TABLE1;
    for another query.
    But where do I put all that code? What language is that code? Is just SQL?
    Thanks

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    That is VBA code; it would go in a standard module. It would need to be modified to account for your specific needs. It would get called from a query. I won't say that what you want can't be done with pure SQL, but I think it would be tricky to get the values from the different records onto one line. I suspect that your actual data is different than the simple sample you've posted, so I can't speculate on the specifics.
    Paul

  7. #7
    Join Date
    Jul 2008
    Posts
    5
    Thanks
    It's just that my bosses asked me some way to do this. And they did want to learn some way to do it by themselves. I might teach them some sql query to do it. But I remember they told me not to do any programming for this purpose just because they wanted to learn a way they could apply by themselves. And they are not programmers. Thanks for the suggestion though because. It is the only one I have so far.

  8. #8
    Join Date
    Jul 2008
    Posts
    5
    As for the overall goal of a solution for this problem is this: my bosses want to generate word documents very quickly by combining the content of the access database with a word document template which links the fields from table1. Having said this, they have this problem: when they generate the word document using the access database combine option, they get a word document with the template repeated for each possible row of the table1, that is to say 6 times. They would like to get a word document with just two repetitions of the template corresponding to the two previously registers mentioned in the query that I want to build.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •