Good Evening (where I am...),
I'm trying to set up an Access database for inspection/survey data. An inspector will conduct a survey where he or she will complete about 20 different checklists in 4 or 5 different categories, each checklist may have from 5 to 20 questions on it with possible answers of yes, no, unknown or not applicable. Each set of 20 checklists will be associated with a particular site/location.
I've established a general inspection information table to capture data like Inspector Name, Date, Inspection ID#, Facility, etc. I originally developed a table for each checklist where I captured the inspection ID# as my primary key and 3 fields for each question item, one identifying the question, the second the response and the third a date indicating when "No" results were corrected to "Yes". Therefore each table might range from 16 fields (5 questions) to 61 fields (for 20 questions). This made it relatively simple to develop a form based on the table that was quite easy to work with.
The problem is, it's not very "queryable". I'm having problems developing a query that will go into the table and only extract those answers that are either Yes or No. With as many as 20 questions in each table, I can't run an "Or" query since this would miss survey where more than one answer was No. I also realize that I can't easily search across all 20 checklists for questions answered with No unless I try to merge all of the Tables under one very large query. It doesn't seem very elegant or efficient for that matter.
My second attempt was to try to capture the answer to each question in each category as a row in a master table. Each record would be comprised of an inspection ID#, a question code to identify it and its category, the answer and a date field for when it was closed. It's pretty simple to query the table for particular answers, but this type of design makes the form harder to develop. Can I have a series of questions on one form, with the answer to each question populating a different record in a table? How would I set this up? Is it a subform application?
I would appreciate any advice that anybody can offer on the best approach to take. I'm starting to learn some basic programming aspects of Access now but I'm definitely a newbie so please be gentle with the tech speak.
Thanks,
Peter
I'm trying to set up an Access database for inspection/survey data. An inspector will conduct a survey where he or she will complete about 20 different checklists in 4 or 5 different categories, each checklist may have from 5 to 20 questions on it with possible answers of yes, no, unknown or not applicable. Each set of 20 checklists will be associated with a particular site/location.
I've established a general inspection information table to capture data like Inspector Name, Date, Inspection ID#, Facility, etc. I originally developed a table for each checklist where I captured the inspection ID# as my primary key and 3 fields for each question item, one identifying the question, the second the response and the third a date indicating when "No" results were corrected to "Yes". Therefore each table might range from 16 fields (5 questions) to 61 fields (for 20 questions). This made it relatively simple to develop a form based on the table that was quite easy to work with.
The problem is, it's not very "queryable". I'm having problems developing a query that will go into the table and only extract those answers that are either Yes or No. With as many as 20 questions in each table, I can't run an "Or" query since this would miss survey where more than one answer was No. I also realize that I can't easily search across all 20 checklists for questions answered with No unless I try to merge all of the Tables under one very large query. It doesn't seem very elegant or efficient for that matter.
My second attempt was to try to capture the answer to each question in each category as a row in a master table. Each record would be comprised of an inspection ID#, a question code to identify it and its category, the answer and a date field for when it was closed. It's pretty simple to query the table for particular answers, but this type of design makes the form harder to develop. Can I have a series of questions on one form, with the answer to each question populating a different record in a table? How would I set this up? Is it a subform application?
I would appreciate any advice that anybody can offer on the best approach to take. I'm starting to learn some basic programming aspects of Access now but I'm definitely a newbie so please be gentle with the tech speak.
Thanks,
Peter
