11,927,440 members (44,163 online)
Rate this:
See more:
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 SQL As String
Dim vFld As Variant

Set cnn = CurrentProject.Connection
vFld = Null

SQL = "SELECT [" & Fieldx & "] as Fld" & _
" FROM [" & Source & "]" & _
" WHERE [" & Identity & "]=" & Value & _
" and [" & Identity1 & "]=" & Value1
' open recordset.

' 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

Posted 28-Jan-13 0:09am

Rate this:

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

ThisItem = Mid(ThisItem, Len(Delimiter2) + 1)

' Concatenate row result to function return value

DConcat = Nz(DConcat, "") & Delimiter1 & ThisItem
.MoveNext
Loop
.Close
End With

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.
v3
Member 8054539 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ő 28-Jan-13 5:46am

Please give a more comprehensive sample input and output.
Member 8054539 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ő 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ő 28-Jan-13 7:55am

See update!
It is working fine.

Top Experts
Last 24hrsThis month
 Richard Deeming 200 F-ES Sitecore 155 Sergey Alexandrovich Kryukov 153 Jochen Arndt 150 Garth J Lancaster 120
 OriginalGriff 7,530 KrunalRohit 4,703 Sergey Alexandrovich Kryukov 3,658 George Jonsson 2,945 Suvendu Shekhar Giri 2,291