You could dump the data to a temp table with the "medium"/"non-medium" data in one record, then use a query to get the data in the format you want (I don't like doing it that way, but sometimes you have to)
Use a Union query??
Use a crosstab query??
I modified your query ...I don't know if it will work since I don't know your field names and I don't know what your data looks like. You could try:
Code:
SELECT ID, iif(Team="Medium",PlayerAverageHeight,"") as MedHt, iif(Team="Medium","",CompAverageHeight) as NonMedHt
FROM MasterDB
If it gives the correct records, then add in the grouping....
Code:
GROUP BY ID, MedHt, NonMedHt