Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C#
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.
Posted 27-Jan-13 18:14pm
Comments
lewax00 at 27-Jan-13 23:43pm
   
Repost of http://www.codeproject.com/Questions/535083/concatationplusqueryplusisplusnotplusworkingplusin
 
If you have additional details to add, please edit your original question. If not, be patient, we aren't paid to do your job, so you might not get an answer right away.
Member 8054539 at 28-Jan-13 2:29am
   
please reply i don't know see the concante function i send the conc function code and help me please.
Member 8054539 at 28-Jan-13 3:00am
   
please kindly update the solution for my above posted question conc function.
help me
Daniele Rota Nodari at 28-Jan-13 6:45am
   
Hi.
I can see that the Conc function expects 6 parameters but only 4 are passed to it; then, the inner SQL uses all the 6 parameters (including the 2 unassigned).

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

  Print Answers RSS
0 OriginalGriff 645
1 Maciej Los 225
2 Abhinav S 171
3 Richard Deeming 160
4 Suvendu Shekhar Giri 159
0 Sergey Alexandrovich Kryukov 9,623
1 OriginalGriff 9,095
2 Peter Leow 5,044
3 Kornfeld Eliyahu Peter 3,373
4 Maciej Los 2,581


Advertise | Privacy | Mobile
Web03 | 2.8.150327.1 | Last Updated 27 Jan 2013
Copyright © CodeProject, 1999-2015
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