TRANSFORM First(Val([PartData])) AS Expr1 SELECT a_tblParts.CarID FROM a_tblParts INNER JOIN a_tblPartHead ON a_tblParts.PartID = a_tblPartHead.PartID WHERE (((a_tblPartHead.Part) In ("GA ","Legal","GV","LRating","Rear","Legal A","GC","Legal D"))) GROUP BY a_tblParts.CarID PIVOT a_tblPartHead.Part;
Answer by Magnus Ahlkvist ·
PIVOT in Transact-SQL is a completely different beast than PIVOT in Access.
I'm not sure what kind of data you have in your table, but it will look a little something like this (with possible needs for some tweaking depending on data in your table).
If I remember correctly how the PIVOT function works in Access, I think this will get you where you want.
Note that I assume PartData contains a string value storing integer values. That might not be the case, you use Val in your Access query, but Val, if my memory is not cheating me, will return a numeric value and if that numeric is a decimal value, a decimal value will be returned. So it might be you want to cast to numeric (5,2) or something like that instead.
Note also that Val in Access is very forgiving while CAST is not. So if you have something like '5 mm' stored in PartData, your query won't work. The string value have to be a proper numeric value. The PARSE function in T-SQL is a bit more forgiving than CAST but not as forgiving as Val in Access.
SELECT a_tblParts.CarID, [GA], [Legal], [GV], [LRating], [Rear], [Legal A], [GC], [Legal D] FROM ( SELECT a_TblParts.CarID, a_TblPartHead.Part, a_TblPartHead.PartData FROM a_TblParts INNER JOIN a_TblPartHead ON a_TblParts.PartID = a_TblPartHead.PartID WHERE a_TblPartHead.Part IN ('GA','Legal','GV','LRating','Rear','Legal A','GC','Legal D') ) as t PIVOT(MIN(CAST(PartData as int)) FOR Part IN ([GA],[Legal],[GV],[LRating],[Rear],[Legal A],[GC],[Legal D])) as p