access crosstab query for subreport with multiple rows

I am needing to get a result from a joined table  for a subreport
I need
RequestID|Qty|ItemNumber|InvoicePrice|correctPrice

here is sample of a dataset with same requestID there can be are mutilple requestID for data
RequestValueID      RequestID      FieldDescription      CreditReasonFieldID      RequestValue      SortOrder
470479      82307      Quantity      3      1      1
470480      82307      Quantity      3      1      1
470481      82307      Quantity      3      3      1
470482      82307      Quantity      3      1      1
470483      82307      Quantity      3      1      1
470484      82307      Quantity      3      2      1
470485      82307      Quantity      3      1      1
470486      82307      Quantity      3      1      1
470487      82307      Quantity      3      7      1
470488      82307      Quantity      3      1      1
470489      82307      Quantity      3      1      1
470490      82307      Quantity      3      2      1
470491      82307      Quantity      3      5      1
470492      82307      Quantity      3      1      1
470493      82307      Quantity      3      1      1
470504      82307      Item Number      4      85006      2
470505      82307      Item Number      4      85863      2
470506      82307      Item Number      4      86212      2
470507      82307      Item Number      4      86213      2
470508      82307      Item Number      4      86230      2
470509      82307      Item Number      4      86445      2
470510      82307      Item Number      4      86448      2
470511      82307      Item Number      4      125032      2
470512      82307      Item Number      4      125039      2
470513      82307      Item Number      4      126035      2
470514      82307      Item Number      4      126039      2
470515      82307      Item Number      4      4-010793-00      2
470516      82307      Item Number      4      4-048493-00      2
470517      82307      Item Number      4      5-18537      2
470518      82307      Item Number      4      5-18541      2
470519      82307      Invoiced Price      5      130.13      3
470520      82307      Invoiced Price      5      51.85      3
470521      82307      Invoiced Price      5      51.05      3
470522      82307      Invoiced Price      5      51.05      3
470523      82307      Invoiced Price      5      11.51      3
470524      82307      Invoiced Price      5      16.02      3
470525      82307      Invoiced Price      5      16.02      3
470526      82307      Invoiced Price      5      58.06      3
470527      82307      Invoiced Price      5      58.06      3
470528      82307      Invoiced Price      5      68.07      3
470529      82307      Invoiced Price      5      68.07      3
470530      82307      Invoiced Price      5      105.37      3
470531      82307      Invoiced Price      5      31.28      3
470532      82307      Invoiced Price      5      188.19      3
470533      82307      Invoiced Price      5      188.19      3
470534      82307      Correct Price      6      120.00      4
470535      82307      Correct Price      6      42.80      4
470536      82307      Correct Price      6      46.00      4
470537      82307      Correct Price      6      46.00      4
470538      82307      Correct Price      6      9.80      4
470539      82307      Correct Price      6      13.70      4
470540      82307      Correct Price      6      13.70      4
470541      82307      Correct Price      6      49.00      4
470542      82307      Correct Price      6      49.00      4
470543      82307      Correct Price      6      49.00      4
470544      82307      Correct Price      6      49.00      4
470545      82307      Correct Price      6      99.70      4
470546      82307      Correct Price      6      4.40      4
470547      82307      Correct Price      6      144.00      4
470548      82307      Correct Price      6      144.00      4
470549      82307      Quantity      3      1      1
470550      82307      Item Number      4      86446      2
470551      82307      Invoiced Price      5      16.02      3
470552      82307      Correct Price      6      13.70      4
Kurt RossAsked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
?
You leave us guessing.

/gustav
0
 
GozrehCommented:
You can not create a crosstab with one RequestID, it can give you only one result for each RequestID.

TRANSFORM First(Table1.RequestValue) AS FirstOfRequestValue
SELECT Table1.RequestID
FROM Table1
GROUP BY Table1.RequestID
PIVOT Table1.FieldDescription;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.