Transposing query result

  • Thread starter Thread starter WSF
  • Start date Start date
W

WSF

Access97
I need to transpose the results of a query that may have, say, more than
one part used on a job, that needs to be displayed as a single row of
job data with the parts used shown across the row, rather than down the
column with repetitious other job data.

That is:

JobNo Date PartNo1 Qty1 PartNo2 Qty2 PartNo3 Qty3 etc etc

Is there a resource someone can point me to to learn how to achieve this?

Cheers,
WSF
 
You need a crosstab query. This is exactly what it is for. Look in Access
Help, there are some examples there.
 
Hello Duane,
I maybe didn't explain clearly enough.
I have a job record table - holding the "header" record data for each
unique job.

Then there is the line item table, linked and related to the Job record
table. This line item table holds the parts used for each job. Sometimes
maybe no parts at all, sometimes say three parts (per job).

In creating a job activity table I need to create a record for each job
where the parts, if any, are copied to a new field at the end of the job
header record (e.g.. in a new table)

e.g.

JobTable
FieldName: JobNo Make Model DateIn DateOut
Record: 12345 Ford Prefect 01-05-05 02-05-05

LineItemTable
JobNo PartNo Qty Price
12345 Widget1 1 $10.00
123345 Widget2 1 $20.00

becomes
ReportTable (wraps!)
JobNo Make Model DateIn DateOut PartNo1 Qty1 Price1 PartNo2 Qty2 Price2
etc if there is more per job.

Hope this is understandable.
Bill
 
The generic concatenate function can return
JobNo .... Parts
12345 Widget1, 1, $10.00, Widget2, 1, $20.00
Isn't that what you want? All the parts would be returned in a single
expression.
 
Hello Duane,
I have downloaded your sample mdb but unfortunately I am working in
Access97 and the sample is 2K - "Unrecognisable format" issue when
opening it.

Rather than creating a concatenated string I need to copy the header and
line item data into another third {Report) table with a single record
per job that is to include any line items from the related "Parts Used"
table as additional fields. Problem is that each job could have from
zero to say three parts per job record, and would need to be held in the
new table as fields labelled
[Part1],[Qty1],[Price1],[Part2],[Qty2],[Price2] etc in each job record.

It is my first attempt at this and I guess it is a form of concatenation
that I need, except that the result is in a table form.

Am I missing something obvious here?

I picture this as kinda complicated and don't expect too much direct
assistance - and I appreciate your help. Similar examples would help so
I can clone and adapt the concept for my own use.
Cheers,
Bill




The generic concatenate function can return
JobNo .... Parts
12345 Widget1, 1, $10.00, Widget2, 1, $20.00
Isn't that what you want? All the parts would be returned in a single
expression.
 
This function should work. If you can't figure it out, come back with a
header record, some detail records, and desired output.

Function:
Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
' this statement is left intact
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
'Dim rs As New ADODB.Recordset
'rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function


--
Duane Hookom
MS Access MVP


WSF said:
Hello Duane,
I have downloaded your sample mdb but unfortunately I am working in
Access97 and the sample is 2K - "Unrecognisable format" issue when opening
it.

Rather than creating a concatenated string I need to copy the header and
line item data into another third {Report) table with a single record per
job that is to include any line items from the related "Parts Used" table
as additional fields. Problem is that each job could have from zero to say
three parts per job record, and would need to be held in the new table as
fields labelled [Part1],[Qty1],[Price1],[Part2],[Qty2],[Price2] etc in
each job record.

It is my first attempt at this and I guess it is a form of concatenation
that I need, except that the result is in a table form.

Am I missing something obvious here?

I picture this as kinda complicated and don't expect too much direct
assistance - and I appreciate your help. Similar examples would help so I
can clone and adapt the concept for my own use.
Cheers,
Bill




The generic concatenate function can return
JobNo .... Parts
12345 Widget1, 1, $10.00, Widget2, 1, $20.00
Isn't that what you want? All the parts would be returned in a single
expression.
--
Duane Hookom
MS Access MVP


WSF said:
Hello Duane,
I maybe didn't explain clearly enough.
I have a job record table - holding the "header" record data for each
unique job.

Then there is the line item table, linked and related to the Job record
table. This line item table holds the parts used for each job. Sometimes
maybe no parts at all, sometimes say three parts (per job).

In creating a job activity table I need to create a record for each job
where the parts, if any, are copied to a new field at the end of the job
header record (e.g.. in a new table)

e.g.

JobTable
FieldName: JobNo Make Model DateIn DateOut
Record: 12345 Ford Prefect 01-05-05 02-05-05

LineItemTable
JobNo PartNo Qty Price
12345 Widget1 1 $10.00
123345 Widget2 1 $20.00

becomes
ReportTable (wraps!)
JobNo Make Model DateIn DateOut PartNo1 Qty1 Price1 PartNo2 Qty2 Price2
etc if there is more per job.

Hope this is understandable.
Bill
Click to expand...
Click to expand...
 
Back
Top