R
Rachel Garrett
I have a problem. I'm trying to do two things with my data that
apparently can't be done together.
The context is an assessment system, where scores for each question
are recorded over time on a 1-5 scale. Questions are assessed at
different times, not all at once. (There were a baseline and midterm
assessment in which all questions were scored; those scores were
"authoritiative" and would override any scores that came before.)
One thing I had to do was create a form where people could changed the
planned target dates for future questions. They didn't want to see
multiple records of the past assessements; they just wanted to see one
date per question. I was able to cobble together the data with queries
so that these are the fields:
Question name
Milestone 1 Status: [expression returns either "Achieved" or
"Planned"]
Milestone 1 Achieved date: [expression returns date the question was
scored at a 1]
Milestone 1 Target date: [expression returns date they plan to get to
a 1, if they didn't score a 1 already]
Milestone 1 Points earned: [expression returns weighted value of the
question, divided by 5 since there are 5 points]
Milestone 2 Status: ""
Milestone 2 Achieved date: ""
....and so on up through Milestone 5.
I would like to be able to group the data by month from July 2007 to
July 2009, to show two lines over time: the total actual points
earned, and the total target points planned.
Since I only really started with SQL Access yesterday, I don't even
know if this is something that can be done with Expression Builder, or
if it has to be in SQL. Any help would be appreciated. Can this even
be done with the way the data is set up?
Thanks,
Rachel Garrett
apparently can't be done together.
The context is an assessment system, where scores for each question
are recorded over time on a 1-5 scale. Questions are assessed at
different times, not all at once. (There were a baseline and midterm
assessment in which all questions were scored; those scores were
"authoritiative" and would override any scores that came before.)
One thing I had to do was create a form where people could changed the
planned target dates for future questions. They didn't want to see
multiple records of the past assessements; they just wanted to see one
date per question. I was able to cobble together the data with queries
so that these are the fields:
Question name
Milestone 1 Status: [expression returns either "Achieved" or
"Planned"]
Milestone 1 Achieved date: [expression returns date the question was
scored at a 1]
Milestone 1 Target date: [expression returns date they plan to get to
a 1, if they didn't score a 1 already]
Milestone 1 Points earned: [expression returns weighted value of the
question, divided by 5 since there are 5 points]
Milestone 2 Status: ""
Milestone 2 Achieved date: ""
....and so on up through Milestone 5.
I would like to be able to group the data by month from July 2007 to
July 2009, to show two lines over time: the total actual points
earned, and the total target points planned.
Since I only really started with SQL Access yesterday, I don't even
know if this is something that can be done with Expression Builder, or
if it has to be in SQL. Any help would be appreciated. Can this even
be done with the way the data is set up?
Thanks,
Rachel Garrett