This sounds very similar to a questionnaire type database but with rooms taking the place of "respondents". Perhaps an adaptation of the data model outlined here can help: Need aid on generating a report from a survey layout.
That is helpful, thank you. But the part you wrote about "You may or may not need a Surveys table. Adding one allows you to process more than one survey--each with it's own group of related records in the Questions table." doesn't elaborate much.
The way I am thinking it in my mind is a field in the Questions table called "Rooms applicable" and then ideally it would just be 1,2,3,4,5,6 the next one could be 2,4,5,6
then when going to a testing layout and choosing room 2, it would show both questions. Where are room 3 would only show the first question. Is it that simple? can it just search the contents of a field? Another option I was thinking was creating different fields for each room - there will probably be about 10, and just flagging Y or N for each field so when you choose a particular room it only searches for questions with Y in the Room1 field.
The purpose to using a Surveys table is so you can associate different groups of questions with different surveys. That sounds very much like what you need here. For a given room, you can link it to a specific "survey" record in order to select the applicable set of questions for that room.
The "rooms applicable" field won't work all that well. You could use a list of Room IDs separated by returns as a match field, but if you want to make the same Question record part of more than one group of questions, (a question used in more than one "survey"), it would be generally a better idea to use a join table instead of a return separated value field in order to set up the needed many to many relationship.
I totally agree the survey table is what I am looking for, the question I have is setting it up, what would be the pk and fk fields between surveys and questions? How would I make it so one particular survey pulls "x" questions and the other pulls "y" questions.
Thanks for your patience, this is all new to me.
I would also like to add I am aiming for this to be automated. Lets say we get a new piece of equipment in a bunch of rooms and I want to add the questions "Does this device turn on and do what it is expected to do" - I then want to be able to somehow simply flag that question with the rooms it should appear in, without having to go and alter survey lists and what not.
Ideally I would have a layout view with each individual room - this would then have three tabs - one for equipment in that room - one for testing questions - one for incident logging. All I want the testing questions to do is to have a portal that simply lists all applicable questions, there is not really a necessary need for logging these answers right now.
what would be the pk and fk fields between surveys and questions?
I believe that this first part is documented in the other thread:
Surveys::__pkSurveyID = Questions::_fkSurveyID
That sets up a one to many relationship. If you want the same question record to be linked to more than one survey record, you can use a join table and set up this relationship:
Surveys::__pkSurveyID = Survey_Question::_fkSurveyID
Questions::__pkQuestionID = Survey_Question::_fkQuestionID
Typically, one uses a portal to Survey_Question on the Surveys layout to select existing question records in order to link them to the current survey record by creating new records in the Survey_Question join table.