I’m tracking questions for a study course. I include an AUDIO version of each question, a VIDEO version of each question, a PRINTED version of each question, and it should appear in the final TEST.
The course uses multiple books, and there are multiple questions in each book.
Also, each question can be in multiple books as the updated versions of the books still have the old information. However, occasionally, the same question may have 2 different answers across 2 or more different books.
For example I have 4 different code books (Code 2006, Code 2009, Code 2012, and Code 2015)
Question - What is the standard width of a door?
Code 2006 answer - 2 inches
Code 2009 answer - 2 inches
Code 2012 answer - 2.25 inches
Code 2015 answer - 2.5 inches
I have a QUESTIONS table with the Question, Answer, and a link to which book that question and answer appears in.
So the question “What is the standard width of a door” with the answer “2 inches” is linked to both the Code 2006 book AND the Code 2009 book.
For each question I want to know if it has been added it to the Audio, Video, Print, and Test. Those fields are in my Production table.
I need to have an occurrence of each question for each book as a unique row so I can know if I have added the question from the Code 2006 book to Audio, Video, Print, and Test. And then separately I need to know the same information for each of the other Code books.
I may have added the question from the Code 2006 book to Audio, Video, Print, and Test but for the Code 2009 I may have only added it to the Audio, Video, and Print and then for the 2012 book, maybe I have only added it to the Test.
What is the best way to have the question entered only once for the combination of question and answer (with links to multiple books), but then have a production table that shows my progress for each question by book?
