query results in single row and multiple columns.

[Davo67]

Hi
Looking for a query to retrieve records from tables and show them on the same row in columns.

I'm using linked tables to a larger system and cannot change main DB.

eg.
Table 1(T1) has name and personid fields.
Table 2(T2) has timesheetid and personid
Table 3(T3) has timesheetid, Lineid, description

The problem is T3 has variable multiple lines & descriptions per timesheet and I want them showing in columns on ONE row

eg.
T1 T2 T3 T3 T3 T3
name timesheet lineid description lineid description
joe 1 1 rate a 2 rate b
pete 2 1 rate b
charlie 3 1 rate a 2 rate b


I hope that this makes sense.

I'm hoping to write an ASP web page to show the data as it has to be exported to excel in this format to upload to a 3rd party system. Therefore I CANNOT change the layout or the database!!

Regards

 

[PHV]

Have a look here:
faq701-4233

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

If you want this to display in multiple columns in an ASP web page, you won't be able to use custom functions defined in Access.

I would write some vbscript that looped through a recordset creating the multiple concatenated values or <td>s depending on how you want the information formatted.

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

I should have included a function I use in my asp pages. This assumes an available adoConn (connection).

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

Duane
Hook'D on Access
MS Access MVP