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

R

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
 
J

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
 
R

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top