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:

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD="align: center"]QuestionID[/TD]
[TD="align: center"]Question Category[/TD]
[TD="align: center"]Question[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Safety[/TD]
[TD]What is your DOB?[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]HR[/TD]
[TD]When were you hired?[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]LP[/TD]
[TD]What is your SSN?[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD="align: center"]QuestionID[/TD]
[TD="align: center"]Week 1 Scores[/TD]
[TD="align: center"]Week 2 Scores[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]12.32%[/TD]
[TD]43.23%[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]32.33%[/TD]
[TD]34.43%[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]25.54%[/TD]
[TD]98.45%[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD="align: center"]DateID[/TD]
[TD="align: center"]TestID[/TD]
[TD="align: center"]Date[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]1/1/15[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD]1/8/15[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[TD]1/15/15[/TD]
[/TR]
</tbody>[/TABLE]

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?

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD="align: center"]QuestionID[/TD]
[TD="align: center"]DateID[/TD]
[TD="align: center"]Week 1 Score[/TD]
[TD="align: center"]Week 2 Score[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]12.32%[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]32.33%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD]25.54%[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]DateID[/TD]
[TD="align: center"]QuestionID[/TD]
[TD="align: center"]Score[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]32.68%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD]72.32%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3[/TD]
[TD]65.24%[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]32.23%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD]58.65%[/TD]
[/TR]
</tbody>[/TABLE]

 

[mole999]

simpler than that
[TABLE="width: 288"]
<colgroup><col style="width:54pt" span="4" width="72"> </colgroup>[TR]
[TD="class: xl63, width: 72"]

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

[/TD]
[TD="class: xl63, width: 72, align: right"][/TD]
[TD="class: xl63, width: 72, align: right"][/TD]
[TD="class: xl63, width: 72, align: right"][/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl64, align: right"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl64, align: right"][/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl64, align: right"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[/TABLE]

 

[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.