question

Yeah,

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