I'm working on a database that contains information about how well a
question was scored on an internal assessment. In order to make the
data work for PivotTables, I have it set up as one record per line.

*The field names are Question Name [text], Score [number], Date
[date], Assessment Type [Text].
*I have a superkey that was concatenated from Question Name, Score,
and Date.

However, the "real" unit that we work with is the question, broken
down by score.

*I need to be able to produce a view with the following fields:
Question Name [text], Date when it scored a 1 [date], Date when it
scored a 2 [date],  Date when it scored a 3 [date],  Date when it
scored a 4 [date],  Date when it scored a 5 [date].

Right now, I have two separate tables. When a user makes changes via
the form, they change it in the second table -- e.g. they can change
"Date when it scored a 1". The VBA in the form runs SQL to append it
to the first table, for the pivots. It is working, but I am sure that
this data duplication will cause issues down the road. Is there a way
to actually combine 5 records into one, with SQL?

Thanks,
Rachel