Select * from Tb_Sch_TIme_Table;
Sno Sch_Date Session Course Faculty_Code
1 12/01/2013 1 AFF GS
2 12/01/2013 1 AFF NR
3 13/01/2013 1 TFC VB
I tried the below query several times, but I am not getting the correct output.
Collapse | Copy Code
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") AS Faculty_Code
FROM Tb_Sch_TIme_Table
GROUP BY Tb_Sch_TIme_Table.Course, [Sch_Date], [Session];
Output as follows using the above query;
Sch_Date Session Course Faculty_Code
12/01/2013 1 AFF GS, NR, VB
13/01/2013 1 TFC GS, NR, VB
The above output is not correct.
I need the output as follows (compare with Select * from Tb_Sch_TIme_Table)
Sch_Date Session Course Faculty_Code
12/01/2013 1 AFF GS, NR
13/01/2013 1 TFC VB
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
How can I achieve this?
I tried several times, but my output is not matching with select * from Tb_Sch_TIme_Table.