Want 5 records --> 1 record with 5 fields. Can this be a query?

[Rachel Garrett]

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

 

[Rachel Garrett]

I think this might be more of a query design question than a table
design question, so I posted it in microsoft.public.access.queries as
well.