Query Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table with the following values:

Date StaffName ExpenseType Amount Details
10/10/2007 Staff1 Medical $50.00 Attached medical
cretificate
10/10/2007 Staff1 Entertainment $100.00 Cleint entertainment
10/10/2007 Staff1 Local Transport $3.50 Train Cahrges
10/10/2007 Staff2 Stationery $12.00 Stationeries for admin
work

I want a result fas follows:

Date StaffName Details Medical Entertainment Local Transport
Stationery
10/10/2007 Staff1 Attached Medical $50 $100 $3.50
cretificate;Cleint
Entertainment;
Train Charges
10/10/2007 Staff2 Stationery
$12.00


In access I got the result as i expect like the above using a user defined
function to concatenate the details and used a TRANSFORM & PIVOT.

BUt my problem is I'm access the database from Internet and the user defined
function is not available when accessing the database from outside.

Is there any other way that I can Acieve this?

Any help would be greatly appreciated.

Thanks in advance.
 
Your question is a web development question based on whatever language you
use for web projects. If you are using ASP, consider this function that
requires an ADO connection named adoConn

Function Concatenate(strSQL, strDelimiter, strSeparator)
'strSQL should be a sql statement that returns a single field or expression
' from a table or query in the order desired in the return
'strDelimiter is placed around each value
'strSeparator is placed between values
Dim strReturn, adoRS
Set adoRS = Server.CreateObject("ADODB.Recordset")
'this next line uses a pre-existing ADO connection named "adoConn"
adoRS.Open strSQL, adoConn, 0, 1, 1
Do While Not adoRS.EOF
strReturn = strReturn & strDelimiter & adoRS(0) & strDelimiter &
strSeparator
adoRS.MoveNext
Loop
adoRS.Close
set adoRS = Nothing
If Len(strReturn) > 0 Then
strReturn = Left(strReturn, Len(strReturn) - Len(strSeparator) )
End If
Concatenate = strReturn
End Function
 
Thanks Duane Hookom.

Yes I am using ASP. But my problem is how can i use this function in ASP to
acheive what i want. I'm bit confused. B'cos the access query does the
TRANSFORM & PIVOT.
 
You will need to write code to step through your recordset and perform
similar aggregates as your crosstab would have done.
 
I got it!...Thanks so much.

Duane Hookom said:
You will need to write code to step through your recordset and perform
similar aggregates as your crosstab would have done.
Click to expand...
 
Back
Top