Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C#
SELECT Tb_Sch_TIme_Table.Sch_Date, Tb_Sch_TIme_Table.Session, Tb_Sch_TIme_Table.Course, Conc("Faculty_Code","Session"[Session],"Tb_Sch_TIme_Table","Sch_Date",[Sch_Date]) AS Faculty_Code
FROM Tb_Sch_TIme_Table
GROUP BY Tb_Sch_TIme_Table.Course, Tb_Sch_TIme_Table.Sch_Date, Tb_Sch_TIme_Table.Session;
 
when i run the above query;
 
output as follows in MS Access;
 
Sch_Date Session Course Faculty_Code
1/12/2013 1 AFF
1/13/2013 1 TFC
 
in that output faculty_Code is not showing from the abovequery what is the mistake help me.
 
in the above query i use conc. for that conc function code as follows;
 
Option Compare Database
Option Explicit
 
Public Function Conc(Fieldx, Identity, Value, Source, Identity1, Value1) As Variant
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim SQL As String
Dim vFld As Variant

Set cnn = CurrentProject.Connection
Set rs = New ADODB.Recordset
vFld = Null

SQL = "SELECT [" & Fieldx & "] as Fld" & _
" FROM [" & Source & "]" & _
" WHERE [" & Identity & "]=" & Value & _
" and [" & Identity1 & "]=" & Value1
' open recordset.
rs.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly

' concatenate the field.
Do While Not rs.EOF
If Not IsNull(rs!Fld) Then
vFld = vFld & ", " & rs!Fld
End If
rs.MoveNext
Loop
' remove leading comma and space.
vFld = Mid(vFld, 3)

Set cnn = Nothing
Set rs = Nothing

' return concatenated string.
Conc = vFld
End Function
 

please correct my query.
Posted 27-Jan-13 23:09pm

1 solution

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

Solution 1

1) Why are you using this function at all? Use subquery[^] instead.
2) You are using this function as aggregate function (not part of the group by clause), are you sure this is what you want?
 
[Update: found[^] a great aggregate concat function]
 
Function DConcat(ConcatColumns As String, Tbl As String, Optional Criteria As String = "", _
    Optional Delimiter1 As String = ", ", Optional Delimiter2 As String = ", ", _
    Optional Distinct As Boolean = True, Optional Sort As String = "", _
    Optional Limit As Long = 0)
    
    ' Function by Patrick G. Matthews, basically embellishing an approach seen in many
    ' incarnations over the years
    
    ' Requires reference to Microsoft DAO library
    
    ' This function is intended as a "domain aggregate" that concatenates (and delimits) the
    ' various values rather than the more usual Count, Sum, Min, Max, etc.  For example:
    '
    '    Select Field1, DConcat("Field2", "SomeTable", "[Field1] = '" & Field1 & "'") AS List
    '    FROM SomeTable
    '    GROUP BY Field1
    '
    ' will return the distinct values of Field1, along with a concatenated list of all the
    ' distinct Field2 values associated with each Field1 value.
    
    ' ConcatColumns is a comma-delimited list of columns to be concatenated (typically just
    '   one column, but the function accommodates multiple).  Place field names in square
    '   brackets if they do not meet the customary rules for naming DB objects
    ' Tbl is the table/query the data are pulled from.  Place table name in square brackets
    '   if they do not meet the customary rules for naming DB objects
    ' Criteria (optional) are the criteria to be applied in the grouping.  Be sure to use And
    '   or Or as needed to build the right logic, and to encase text values in single quotes
    '   and dates in #
    ' Delimiter1 (optional) is the delimiter used in the concatenation (default is ", ").
    '   Delimiter1 is applied to each row in the code query's result set
    ' Delimiter2 (optional) is the delimiter used in concatenating each column in the result
    '   set if ConcatColumns specifies more than one column (default is ", ")
    ' Distinct (optional) determines whether the distinct values are concatenated (True,
    '   default), or whether all values are concatenated (and thus may get repeated)
    ' Sort (optional) indicates whether the concatenated string is sorted, and if so, the
    '   columns used for the sort.  As you would in an ORDER BY clause, use Asc or Desc to
    '   indicate whether the column is sorted ascending or descending.  If Asc/Desc is
    '   omitted, Asc is assumed by the query engine
    ' Limit (optional) places a limit on how many items are placed into the concatenated string.
    '   The Limit argument works as a TOP N qualifier in the SELECT clause
    
    Dim rs As DAO.Recordset
    Dim SQL As String
    Dim ThisItem As String
    Dim FieldCounter As Long
    
    On Error GoTo ErrHandler
    
    ' Initialize to Null
    
    DConcat = Null
    
    ' Build up a query to grab the information needed for the concatenation
    
    SQL = "SELECT " & IIf(Distinct, "DISTINCT ", "") & _
            IIf(Limit > 0, "TOP " & Limit & " ", "") & _
            ConcatColumns & " " & _
        "FROM " & Tbl & " " & _
        IIf(Criteria <> "", "WHERE " & Criteria & " ", "") & _
        IIf(Sort <> "", "ORDER BY " & Sort, "")
        
    ' Open the recordset and loop through it:
    ' 1) Concatenate each column in each row of the recordset
    ' 2) Concatenate the resulting concatenated rows in the function's return value
    
    Set rs = DBEngine(0)(0).OpenRecordset(SQL, dbOpenForwardOnly)
    With rs
        Do Until .EOF
            
            ' Initialize variable for this row
            
            ThisItem = ""
            
            ' Concatenate columns on this row
            
            For FieldCounter = 0 To rs.Fields.Count - 1
                ThisItem = ThisItem & Delimiter2 & Nz(rs.Fields(FieldCounter).Value, "")
            Next
            
            ' Trim leading delimiter
            
            ThisItem = Mid(ThisItem, Len(Delimiter2) + 1)
            
            ' Concatenate row result to function return value
            
            DConcat = Nz(DConcat, "") & Delimiter1 & ThisItem
            .MoveNext
        Loop
        .Close
    End With
    
    ' Trim leading delimiter
    
    If Not IsNull(DConcat) Then DConcat = Mid(DConcat, Len(Delimiter1) + 1)
    
    GoTo Cleanup
 
ErrHandler:
    
    ' Error is most likely an invalid database object name, or bad syntax in the Criteria
    
    DConcat = CVErr(Err.Number)
    
Cleanup:
    Set rs = Nothing
    
End Function
 
The sample query from the comment is for string type "foreign key", remove extra quotes if you have numeric type.
  Permalink  
v3
Comments
Member 8054539 at 28-Jan-13 5:33am
   
how to use subquery. and get my ouput.for that how can i do the subquery.pleae help me. how can i do. and get the correct ouput.i mentioned my output as above.
Zoltán Zörgő at 28-Jan-13 5:46am
   
Please give a more comprehensive sample input and output.
Member 8054539 at 28-Jan-13 6:37am
   
this is my intput SELECT Tb_Sch_TIme_Table.Sch_Date, Tb_Sch_TIme_Table.Session, Tb_Sch_TIme_Table.Course, Conc("Faculty_Code","Session"[Session],"Tb_Sch_TIme_Table","Sch_Date",[Sch_Date]) AS Faculty_Code FROM Tb_Sch_TIme_Table GROUP BY Tb_Sch_TIme_Table.Course, Tb_Sch_TIme_Table.Sch_Date, Tb_Sch_TIme_Table.Session; when i run the above query; output as follows in MS Access; Sch_Date Session Course Faculty_Code 1/12/2013 1 AFF GS,NR 1/13/2013 1 TFC VB select * from Tb_SCh_TIme_Table; as follows; the above output should match which select * from Tb_SCh_TIme_Table; Sno Sch_Date Session Course Faculty_Code 1 1/12/2013 1 AFF GS 2 1/12/2013 1 AFF NR 3 1/13/2013 1 TFC VB how can i get the ouput.help me the above one is my input and output.
Zoltán Zörgő at 28-Jan-13 7:21am
   
And what do you have in Tb_SCh_TIme_Table? But I began to understand what you want: you need a string concatenation aggregat, like SUM, but with strings, right?
Zoltán Zörgő at 28-Jan-13 7:55am
   
See update! It is working fine.

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

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 Shai Vashdi 1,518
1 OriginalGriff 383
2 Manas Bhardwaj 324
3 Tadit Dash 305
4 Peter Leow 255
0 Sergey Alexandrovich Kryukov 9,575
1 OriginalGriff 5,856
2 Peter Leow 4,405
3 Maciej Los 3,540
4 Abhinav S 3,513


Advertise | Privacy | Mobile
Web04 | 2.8.140415.2 | Last Updated 28 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Use
Layout: fixed | fluid