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

 

[Jake]

I don't know if this can be done in Access but the SQL command for this is
UNPIVOT. Hopefully that will give you a direction to look in.

Jacob

 

[Rachel Garrett]

I don't know if this can be done in Access but the SQL command for this is
UNPIVOT. Hopefully that will give you a direction to look in.

Thanks -- it looks like UNPIVOT would help, but it is not available in
Access.

--Rachel

 

[Rachel Garrett]

I found the CROSSTAB query wizard! Question answered.