Tags: access, collcted, corelations, database, department, exporting, hired, merging, microsoft, mysql, oracle, performance, rows, spss, sql, statistician, student

Exporting data to SPSS, merging many rows into one.

On Database » Microsoft Access

7,802 words with 4 Comments; publish: Fri, 04 Jan 2008 12:00:00 GMT; (250109.38, « »)

Hello,

Our department has hired a statistician to do some corelations on data I have collcted. The data is on student performance while student teaching. Each student is observed for a mid-review and final. Each student is observed by 1) the teacher, 2) the supervisor and 3) themselves. Total of 6 reviews for each student. The table which contains the data looks something like this:

Code: ( text )
  1. tblFeedback (feedbackid, studentid, evaluatorid, observationid, 1, 2, 3, 4, 5)
  2. feedbackid is primary key
  3. studentid is student identifier (person who is being evaluated)
  4. evaluatorid is who did the evaluating (1, 2 or 3) (supervisor, teacher, student)
  5. observationid is mid or final observation (1 or 2)
  6. and the numbers are the questions on the review form with a possible value of 1-5.

The statistician is working with SPSS and wants all the data on one student to be in one row. He wants the output to look something like:

tblFeedbackOutput(studentid, evaluator_observation_1, evaluator_observation_2, evaluator_observation_3)... etc. So there would essentially be 6 columns in the same row for each question.

My questions are:

1) what is an easy and elegant way to achieve this? It is likely that we will be expanding this process to many different feedback forms that we collect on achievment. I would like to do this in SQL but I don't think it is possible.

2) Is this even necessary? It seems to me that SPSS should be able to handle relationships in a database. This seems like a lot of effort for a very small sample size. Further more it does not seem feasable to define a table in the way that the statistician is asking for. This would give me 240+ fields per record.

Hopefully I have expressed the situation clearly. I would be glad to clarify anything. Thanks for your time!

All Comments

Leave a comment...

  • 4 Comments
    • Quote:
      === Original Words ===

      Hello,

      Our department has hired a statistician to do some corelations on data I have collcted. The data is on student performance while student teaching. Each student is observed for a mid-review and final. Each student is observed by 1) the teacher, 2) the supervisor and 3) themselves. Total of 6 reviews for each student. The table which contains the data looks something like this:

      Code: ( text )
      1. tblFeedback (feedbackid, studentid, evaluatorid, observationid, 1, 2, 3, 4, 5)
      2. feedbackid is primary key
      3. studentid is student identifier (person who is being evaluated)
      4. evaluatorid is who did the evaluating (1, 2 or 3) (supervisor, teacher, student)
      5. observationid is mid or final observation (1 or 2)
      6. and the numbers are the questions on the review form with a possible value of 1-5.

      The statistician is working with SPSS and wants all the data on one student to be in one row. He wants the output to look something like:

      tblFeedbackOutput(studentid, evaluator_observation_1, evaluator_observation_2, evaluator_observation_3)... etc. So there would essentially be 6 columns in the same row for each question.

      My questions are:

      1) what is an easy and elegant way to achieve this? It is likely that we will be expanding this process to many different feedback forms that we collect on achievment. I would like to do this in SQL but I don't think it is possible.

      2) Is this even necessary? It seems to me that SPSS should be able to handle relationships in a database. This seems like a lot of effort for a very small sample size. Further more it does not seem feasable to define a table in the way that the statistician is asking for. This would give me 240+ fields per record.

      Hopefully I have expressed the situation clearly. I would be glad to clarify anything. Thanks for your time!

      I haven't used SPSS but I use SAS. From what I have heard, SPSS is more user-friendly but not as powerful. If I understand your question correctly, what you're asking is not possible in SAS and by extension, SPSS.

      But in Access/SQL, you could create a query with many DLookUp fields. This would however lead to many records with duplicate entries. But you could probably just Select Distinct.

      #1; Fri, 04 Jan 2008 12:01:00 GMT
    • I've just had a few more minutes to think about it and I realized it's entirely possible to do it in SAS/SPSS.

      He'll have to break the data set into multiple data sets, rename the variables, and merge them back together. So tell him to get off his butt and get to work.

      #2; Fri, 04 Jan 2008 12:02:00 GMT
    • thanks, your post gave me some ideas. In Access, I first made a select distinct query on the field I wanted to group on (person identifier). Then I used 6 queries to get each dataset I needed. Finally I bound each of the 6 queries to the select distinct "master list."

      Quote:
      === Original Words ===

      I've just had a few more minutes to think about it and I realized it's entirely possible to do it in SAS/SPSS.

      He'll have to break the data set into multiple data sets, rename the variables, and merge them back together. So tell him to get off his butt and get to work.

      #3; Fri, 04 Jan 2008 12:03:00 GMT
    • Glad I could help. Good luck.
      #4; Fri, 04 Jan 2008 12:04:00 GMT