Was needing some help tracking scores on a weekly basis. Should I use Access? Pivot tables? IDK!

[ranopano]

I need to build a report for my work that tracks the trends of a weekly questionnaire that gets sent out, but I am really struggling trying to develop a way too set this up. Should I use Access? Queries? I don't know!! I know I've probably been overthinking this and there is likely a simple solution, so I was hoping to get some input from yall!

So, the questionnaire contains 100 different questions. All of the questions fall under 1 of 5 different categories or subjects. The questions and categories will always be the same, regardless of the week.

Each week I receive a new report with an average of the score for each question (so say question 39 has a 53% pass/fail score). I currently have about 100 weeks worth of these scores.
See sample tables below to get an idea of what I am talking about:

QuestionID	Question Category	Question
1	Safety	What is your DOB?
2	HR	When were you hired?
3	LP	What is your SSN?

<tbody>
</tbody>

QuestionID	Week 1 Scores	Week 2 Scores
1	12.32%	43.23%
2	32.33%	34.43%
3	25.54%	98.45%

<tbody>
</tbody>

DateID	TestID	Date
1	1	1/1/15
2	2	1/8/15
3	3	1/15/15

<tbody>
</tbody>

What I am trying to do is create something (ideally a pivot table/chart) that can track these results a couple of different ways (that's why I was thinking pivot table/chart so I can incorporate slicers). But regardless, I gotta be able to keep track of the date, question, and the scores associated with each date. So I want to be able to say "the results of question 3 increased from January to April" or something. Just a way of tracking the date and scores associated.

What's tripping me up right now is I don't know how to relate the scores to a date. If you see in the 2nd table, I can link the question to a score no problem, but how would I put the DateID in there too?

QuestionID	DateID	Week 1 Score	Week 2 Score
1	1	12.32%	?
2	1	32.33%
3	1	25.54%

<tbody>
</tbody>

I suppose I could create a new table for each week's result, but I have so many that I would have 100s of tables!

Anyways would really appreciate some feedback or suggestions in creating something.
THank you!

 

[ranopano]

Any suggestions??

 

[mole999]

how many weeks?

I'd almost be tempted to store the data in Access, then use excel to select what you want to report on and display

 

[ranopano]

I have 52 weeks for 2015, plus the ongoing 2016 weeks. So 53 total so far.
Yeah, i like your idea of using access like that. That was my thought as well.
Same question as before though, how would I link the DateID, QuestionID, and TestID? Would each week need to be done a new table? Ideally I'd like to have all weeks and scores on one table just for simplicity, but then I run into the problem shown above with the DateID. I suppose I could also just make one really long table with all 53 weeks worth of scores on one column, but that doesn't seem like the best way to do it!

 

[mole999]

a single record for each week so you have your week number / date, then each of the values 1 to 100. Saying that 110 columns by 53 weeks expanding would easily be handled by excel as a flat table, which could as a named range become a pivot. It really depends on the time available and speed required

 

[ranopano]

Ok, are you talking about something like this:

DateID	QuestionID	Score
1	1	32.68%
	2	72.32%
	3	65.24%
2	1	32.23%
	2	58.65%

<tbody>
</tbody>

 

[mole999]

simpler than that

Excel Workbook A B C D 1 DateID 1 2 3 2 32.68% 72.32% 65.24% 3 32.23% 58.65% Sheet1

<colgroup><col style="width:54pt" span="4" width="72"> </colgroup>

 

[ranopano]

Interesting, I like this idea.
Where would the QuestionID play into this though? I need to have the QuestionID, DateID and score all related to one another.

 

[mole999]

I thought your questions are the same

so the week value goes in Col A
questions are listed across B1 to BB1 or whatever 100 is
Row 2 to 54 are the results score each week

 

[ranopano]

Sorry I didn't make myself clear!
I meant that the questions are the same week-to-week, but there are 100 different questions in total.

Thank you for your help mole999! I'll give this a shot and if any issues come up I'll post again.