Just about to start a new db. suggestions on how to implement, greatly appreciated..
This is how i have it in my head at the moment...
30 odd sets of questions, around 70 questions per set. Idea is:-
Each set of questions, stores as a table, eg: Question set 1. Question Set 2.
Data table with those questions, PLUS the responses, (Y/N,. plus text info)
Start a new question set, by copying the question set selected, into, the Data table, then gather the responses.
Ideally, i want to run this from a windows tablet, and then semi-automate the process. so that tablet runs the access program, collects the responses, plus any photographs, when the tablet plugs into a pc, OR send the data to a usb stick, i want the same access program on the tablet, to copy over that question set, along with the renumbered photographs, to a directory on a shared drive. (from the pc, or the USB stick)
The PC version of the access program, will poll a specific directory, then read the shared drive, looking for the file, to read into the database, along with the photographs.
Each photograph, will be renumbered by the tablet, to correspond with the specific Question Set, question, and Date - to link the picture(s) with the question.
New Database Design Suggestions
-
- 4StarLounger
- Posts: 419
- Joined: 04 Feb 2010, 11:46
New Database Design Suggestions
----------------------------------------------------------------------------------------
Owing at LEAST 34 beers to other helpful forum members. Especially HansV!
Owing at LEAST 34 beers to other helpful forum members. Especially HansV!
-
- Administrator
- Posts: 78574
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: New Database Design Suggestions
Unless the three question sets have a different structure, I'd store them in a single table:
I'd also create a table with the names of the question sets:
Depending on how you want to set up the questionnaires, you may want to add extra fields, such as response type and possible responses.
When the user selects a question set, you copy the QuestionIDs of the corresponding questions to the Data table.
Does that help?
I'd also create a table with the names of the question sets:
Depending on how you want to set up the questionnaires, you may want to add extra fields, such as response type and possible responses.
When the user selects a question set, you copy the QuestionIDs of the corresponding questions to the Data table.
Does that help?
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 12618
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: New Database Design Suggestions
Could one question (such as "What is your name?") appear in multiple question sets? If so then you may want a more complex structure.
StuartR
-
- Administrator
- Posts: 78574
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: New Database Design Suggestions
That's a good point, Stuart. How to handle it depends on the question sets - it might be worth making the structure more complex, or not...
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 419
- Joined: 04 Feb 2010, 11:46
Re: New Database Design Suggestions
Thanks Guys.. yes, i realise i'm not doing things optimally here.. however, data optimisation is definately , not my forte....
There will be multiple questions that are the same, in different question sets. there are 30 question sets.. and the same question might appear 10 times out of the 30.
also, the person responsible for answering the question, will be different for some of the questions. how do i manage that.
What i am worried about is database size... the 70 questions are about 80-90 chars per question. i'm going to copy those questions, each time i do the 'survey' into the main data table.. then add the answers into that table.. (which will easily allow me to query the single data table with ease)
I realise for Efficiency sake, its way better to store each question, in a question table, then, create another table for each survey, which has the question to be asked as a numeric id.. then do the lookup on the form.... then add those responses to the data table.
am i making sense
There will be multiple questions that are the same, in different question sets. there are 30 question sets.. and the same question might appear 10 times out of the 30.
also, the person responsible for answering the question, will be different for some of the questions. how do i manage that.
What i am worried about is database size... the 70 questions are about 80-90 chars per question. i'm going to copy those questions, each time i do the 'survey' into the main data table.. then add the answers into that table.. (which will easily allow me to query the single data table with ease)
I realise for Efficiency sake, its way better to store each question, in a question table, then, create another table for each survey, which has the question to be asked as a numeric id.. then do the lookup on the form.... then add those responses to the data table.
am i making sense
----------------------------------------------------------------------------------------
Owing at LEAST 34 beers to other helpful forum members. Especially HansV!
Owing at LEAST 34 beers to other helpful forum members. Especially HansV!
-
- Administrator
- Posts: 78574
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: New Database Design Suggestions
Do you mean that within one questionnaire some of the questions will be answered by one person, and other questions by someone else?also, the person responsible for answering the question, will be different for some of the questions. how do i manage that.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 419
- Joined: 04 Feb 2010, 11:46
Re: New Database Design Suggestions
Yes, the same question, could have multiple people responsible.. but i'm sure i can find a way.
I am restructuring it, so the questions are in a single table. I am sure i will have more questions about linking,, but you never know i might figure it out.. hehe
I am restructuring it, so the questions are in a single table. I am sure i will have more questions about linking,, but you never know i might figure it out.. hehe
----------------------------------------------------------------------------------------
Owing at LEAST 34 beers to other helpful forum members. Especially HansV!
Owing at LEAST 34 beers to other helpful forum members. Especially HansV!