TRANSFORM in Access does a pivot, but I'm not clear on what the pivot is even supposed to do in this case.

Could you post some original data (well the data structure with some fake data if you want), and what it should look like after the transform?