Questionnaire Database Design
I have designed a small questionnaire and i want to use Ms Access for data entry and storage.
Here is the brief description.
2.date of birth
B.Sports Information about individual
1.do you play any spot ------yes/no
2.what spot do you play -----1.football 2.tennis 3.other
3.is your father still alive-----yes /no
4.what spot does he play------1.football 2.tennis 3.other
5.at what age did you start school
6.what is your political affiliation 1.liberal 2.centre . other
C.For official use only
1.Date of interview-------
2.How enthusiastic was the respondent----1.very 2.Average 3. Below average
How can i go about designing a relational database for the above questionnaire.
I need advice on the relational schema
I am trying to normalize my database by following some of the forum threads on the topics. However, Im getting a little confused
I have created the following Questions Table (My answers will go in a seperate table)
QuestionID - PK
QuestionnaireTypeID - FK from tblQuestionnaireType
QtypeID - FK from tblQtype - listing Y = Yes/No, N = Numeric, T = Text
Qorder - The order the questions should appear, for that particular Quesitonnaire Type
Question - The actual text of the question
Some of my questions are in "Option Groups" - with for example 3 different options (radio buttons), or they will have a combo box with a number of different answers... How should I represent these in this structure?
Do I need to create a new table listing what the options are? (which seems messy - and doesnt seem to achieve the objectives of normalization), should I create a new column in my tblQuestions to group related questions together? (subgroups of questions...in which case I would need an additional subgroup "order" field? How would I deal with the mutually exclusive nature of these questions?) Does this make any sense??
Thanks for any help
To start: Running Office 20003. I have created a survey with Frontpages wizard. Basically It has a topic and the end user rates that topic on a scale of one to five like so:
1. Work Environment
a. I enjoy my work place. 12345
b. I think my office has a good reputation. 12345
1-5 are radial buttons. It takes the users input and stores it in an access database as im sure you all are aware. Looks kinda like this:
1a 1b 2a etc.
3 5 1
What i would like to do is this: When the users are done submitting i would like to go back and take results. I.e. we had this many users select option 3 for question 1a and we had _ number of users select option 2 for 1a, etc.
The thing is i was wondering if there was a way to create a query for it. I know i could just put =1 or =2 but that would require running 5 queries on each question.... not fun. Anyway, any help would be appreciated.
To use, open frmSurvey and click the ! command button to start the survey. Allows you to set up different surveys using the same db with different questions and types of questions for each survey. Hope it's useful to you.
I class myself among "intermediate" Access users--I'm fairly comfortable with the program as a whole, but claim no particular Developer expertise.
I am trying to set up a database to handle a kind of Quality Assurance Medical Records Survey. In principle, it seems like a general Questionnaire/Survey type of application: I want to be able to store the results of questions like:
1. Is documentation legible? (yes/no/NA)
2. Are records present for every billable contact? (yes/no)
3. Did clinician sign every entry? (yes/no)
A bit of trial and error, combined with a little research tells me that rather than setting all this up in one table, I would do better to set up a couple of tables. --That this will make it easier to perform crosstab queries of my results. That is, rather than setting up a table like this:
CLINICIAN [in charge of chart]
It would be better to have:
TABLE: SURVEY RESULTS
(Make sense so far?)
Setting up the tables makes sense to me, and seems to work well. I'm running into trouble setting up a form for data entry!
It seems logical to me to have a form for SURVEY, with a Subform for SURVEYRESULTS. I would like to be able to have all the "Questions" appear on the form, and then have the user just run through the form quickly clicking on Yes, No, or NA for each item.
I'm stumped on how to create this.
Does this question make sense?
Can anyone out there give me a hand?
I have an existing database, that is a mess.
It is basically one table with some lookup tables that contain repetitive data like product categories.
The field code is the product code for a specific product, it was not set as no duplicates. Most products are in specific product categories, but some products are in multiple categories. To work around this, the products were just copied in multiple times and each record for the same product was given a different category.
I am in the process of normalizing the database, and making it more relational. I understand this is something I need to correct 1st.
Any tips on steps to correct this as I do not appear to be looking at this ?
I just keep starring at the wall.
I have the task of creating a new questionaire and associated reporting. Normally I would have done this in Excel, but due to the size I feel it is neccessary to create this in Access.
My question is should I revise my table of completed forms? I initially planned to create a form to populate the completed forms table. Job done?
However I am sure I have read somewhere this is not the best in terms of reporting and I should have a table of answers for each question? Is this correct I have approx 50 questions?
I have the following tables:
1) Staff Details
2) Table Of Questions
3) Table Of Answers (Questions are multiple choice)
4) Table of completed forms
Wondering if anyone can suggest how I might be able to deal with a questionnaire. I thought about doing a straightforward database with all the questions listed as fields in the one table but I think there are too many questions/fields (about 200, definitely not 254 but still raises an error message of "Too Many Fields Defined). The questionnaire will be an on-going process, with users re-submitting their answers every quarter. I do want to be able to analyze the data and create reports, etc. so am hesitant about using Excel. For instance, I did a basic report involving Excel and then adding many columns to calculate various averages - the report was made in Word and I mail-merged in the data from the spreadsheet. Long-winded for just one report and I'm hoping to avoid this.
Any suggestions are welcome.
Thanks and regards.
Hi, I am trying to construct a questionnaire that asks a series of questions (one at a time) to multiple users who have to answer A or B. The users will be performing the questionnaire at the same time.
I want the questionnaire to run as follows:
1) People select their name in a form (Form1)
2) A series of questions (from the tblQuestion table) are presented to the user (Form2)
3) A table is populated which records the following info
* Response_ID (Autonumber)
* User_ID -> based on their selection in form 1
* Question_ID (number) -> taken from the tblQuestion table
* Response_Value) (Text = A or B) -> as submitted by user
I presume I need to set up the following tables:
Table 1 - tblUser
* User_ID (number)
* User_Name (Text)
Table 2 - tblQuestion - the table that contains the actual questions
* Question_ID (Number)
* Question_Description (Text)
Table 3 - tblResponse - the table that gets populated
* Response_ID (Autonumber)
* Question_ID (number)
* Response_Value (text)
My problem is that I am not used to adding records to a table (via forms) as this is the first time I have to run the questionnaire for multiple users (previously i just placed the "A or B" textfield in the tblQuestion, but this is not possible with +100 users.
Could someone please give me some advise as to how to get started with this, in particular at the Form level.
Thanks in advance, Steve
I'm building a questionnaire/survey database for my work. Everything seems pretty straightforward, but I'm a little unsure about how to store the survey configurations.
I'm assuming that the survey will change sometime between now and the end of time so I'm tracking survey configurations (which questions and in what order). Right now my "Configuration" table has two fields: ConfigID (Autonumber), Questions (Memo). The 'Questions' field is a space seperated list of question IDs. The other way I could've done it is to use three fields: ConfigID (Long), OrderNum (Int), QuestionID (Long).
I figure the first way is more memory efficient in the long run and it will take much less time to add a new configuration.
The way I'm doing things now works for me, but I'd like to know if anyone thinks I'm setting myself up for trouble down the road or if my way is actually less memory efficient (I'm not as concerned with processing time).
I currently use DAP to build questionnaires that feed a db. I'd like to be able to ask a follow-on question based on a threshold from a previous question. In other words, if a respondant answers a question below a certain threshold, I'd like to ask another question, and then return to the survey where they left off.
Hello everyone. Im relatively new to access, i've only been using it for a few weeks. There are certain aspects that I cannot get my head around.
Is anyone willing to let me email them what I have done so far and the criteria for the database I am making and advise me where I am going wrong and where I need to be heading to get it right?
Thanks very much. Any help will be very much appreciated.
When creating a database is it true that ideally i should avoid using the lookup wizard at table level and instead do that with combo boxes at form level ?
I have three tables
1 tblAssistFMWork in which is stored the fields JobNumber, SiteRefNumber, SurveyorNo
2 tbltable1 which has the field SiteRefNumber, Address1, Address2 etc
3 tblAssistFMSubJobNumbers which has the fields JobNumber, SubJobNumber.
JobNumber is the relationship between tables 2 and 3 and SiteRefNumber is the relationship between tables 1 and 2. There are other fields in each table but these are the relevant ones for this question.
Each JobNumber will have a minimum of one SubJobNumber but may have many. Each SubjobNumber starts at 1 for each new job so this may look like this in the table
Each SiteRefNumber may have one or many JobNumbers
I wish to enter the date a SubJobNumber is completed and have been struggling with pulling the record into a form using a combo box because of the need to enter the JobNumber and also the SubJobNumber. So I decided to create a new table tblAssistFMJobDateCompleted which has the fields
JobNumber, SubJobNumber, DateJobCompleted
I need to report on a jobs status ie done or not done, but when I created the query for this it would only pull the records which have a completed date on them.
Could someone please give me some advice as to a way forward.
I have been reading other posts, and this where i should have started from the beginning,I am not very experienced with access (or forums as yous propably know) but can learn anything in no time (hopefully).
I would like to design a database to store all the info about a drag racing event including;
• event info (Event name, track name, event date)
• car info (Car name, gearbox type, diff ratio, engine name,)
• driver info (driver firstname, driver lastname)
• Engine info (make, model, engine compression, carburetor, front carburetor jets, rear carburetor jets, fuel pressure, engine timing at idle, total engine timing)
• Race info (track temperature, relative altitude, milibars, humidity, air temperature, tyre type, tyre size, tyre pressure hot, tyre pressure cold, stage RPM, shift RPM, reaction, 60foot time, 330foot time, 660foot time, 660foot MPH, 1000foot time, elapsed time, elapsed MPH, comments.)
My issue is design of tables and relationships. as you can probably see, obviously cars will do many runs at each event, and there engine setups and car setups(diff ratio) can differ each run. there might also be different drivers for the same car. weather info like track temp etc also change from race to race so I think it is possible I might need some sort of race time as-well. My main reason for the database, apart from organizing all my results would be so that I could do a run at an event and search previous races by the current runs weather info (relative altitude, milibars, humidity, etc) as to set the car up similar to keep it’s elapsed times consistent. Please, please, please help as I am at a total loss.
Thanks in advance!!
I've created a database from scratch. No data attached anywhere yet - I'm planning to input the data manually through my Form.
Well I have about 15 tables - CustomerInfo_tbl is the main table with the field RefNumber my primary key (it's an Autonumber which is sequentual). The rest of the tables are normal tables which I plan to link.(these tables also have RefNumber fields which are LongInteger) All the tables have about 10 fields in them.
So then I have 2 choices - I can make one big table (about 150 fields) or 15 tables with 10 fields each and the join them. I heard somewhere that it's better to have small tables and then link them up. So I'm going the smaller tables route.
I'll tell you what I did. I joined all the tables to my CustomerInfo_tbl with a one-to-one join. Then I made a Query, and then I set the RefNumber of the other tables equal to the RefNumber of my CustomerInfo_tbl.
I have 1 problem. When I enter some data into the form (I do not enter all the fields I leave some blank) it isn't displayed in the query, but it is displayed in the appropriate table. Only when I enter all the fields it is displayed in the query correctly and also in the tables.
Any help? I hope I made my question clear enough.
Thank you in advance.
Hi, i got back to working on databases again for the first time in years and ive got confused on the offset.I went to set about desigining my tables and cant figure out how to do it.most of its simple stuff however this one bit stumps me.
Im designing a database to hold stock information in work we manufacture parts for rally cars and when a customer rings up i need to have the price at hand.I can store the prices etc of individual parts,however some people request kits which would have there own stock number and all the same relevant data i.e. Item Name,Type,Cost,Number in stock,Selling Price.......
These kits would consist of several items from the stock table.and when a kit is sold it will be inserted into the Sold Items Table together with the other individual parts a Customer may order.
Any help would be gratefully recieved.
I have a new project but before I get started, I need some advice on design.
I'm designing a student report card which I'll implement for 1300 students next year. The concept is an ongoing electronic markbook where teachers update the progress of each student and then at the end of the year print a report based on the captured data. The data is then passed on to the next teacher the following year.
My problem is data storage. During the course of a year a teacher may mark off up to 550 things a student can do. After 7 years of schooling (7 x 550) this amounts to a large amount of data. The thing is, I could reduce the number of fields if the multiselect list box stored multiple pieces of data. Option groups are no good because only one piece of data is stored and I certainly don't want thousands of check boxes.
Basically, I'm looking for a way to store multiple pieces of data in the one field. I'm also looking for a way in reducing the amount of clicks (i.e. one click instead of 2 to enter data)
I can't delete data because it's an audit requirement to keep it.
I appreciate any help
Can someone help me out with the design of my database? I currently have it set up one way and I am running into problems when I need to update information?
here are the attributes I have to put in the database for Printer cartridges:
There can be numerous products with the same name
(for example: C120 is a product name but can be made by HP and by Canon)
There can be numerous products with the same ProductCode
(for example: F416301700 is a product code but can have multiple products with that code)
Currently I have it set up like this:
I have created that combine table to link the two other tables together.
I am running into problems now when i want to update a product and I don't know where to take this from here. I think if I have a better design from the get go it will prevent problems in the long run.
Can anyone help me with this?
Hi, I’m fairly new to access and I’m creating (well, want to create) an employee database. I was wondering if anybody has created an employee database that keeps track of employees information like; personal information, salary, training the employees have taken, performance reviews and much more and if so do you have any suggestions on the design. Pretty much this database will be an electronic version of the employees personnel file.
Or does anybody know of any sample databases, not necessary an employee database that have been shared here in the past that I could look at and get ideas (formulas, qrys, tbls. etc.) from there.
I am designing a db for my car event that happens once a year.
It needs to hold car info like engine specs, car specs. Entrant details and event details, merchandise info.
I started out designing on paper (Can’t stress how much this helped a definite must for all noobs and experienced I would imagine) and went from there.
Ended up using northwind and modified it to suit my situation.definately recommend this to beginners as it gives you a good look at databases and great starting point. anyway this db has everything I need I think, the db is for holding info about participants in a car event. There is a form that the entrant would fill out asking there details and the car details. There is also a section on the form to buy merchandise as the car event has its own T-Shirt and DVD.I have added this into the database but I am having trouble finding where to put the T-Shirt Sizes.
E.g. there has been 3 events so far (1 per year), car event 1, car event 2, car event 3.each event has had a T-shirt. this t-shirt comes in sizes, xxs,xs,s,m,l,xl,xxl,xxxl,xxxxl,xxxxxl.my problem is do you add the same t-shirt in the products table 10 times just with different sizes.
also the Entrants form has subform event, where you select the event each year as to not have to enter entrants twice.i can not seem to figure this out either.
Once you look at the db you will have a better idea.
The database only has a very standard form for getting around while I complete the design, all the beautification will come after this stage.
Any other ideas would be greatly appreciated.
(PLEASE COPY AND PASTE THE LINK INTO YOUR BROWSER OR IT WILL NOT WORK DUE TO REMOTE LOADING)
If anyone can give me some friendly advice in design I would really appreciate it!!
I have a database of people who are assigned to one location at any given time (usually yearly). They may have up to six jobs in this location. A job may have its own location that is different from the assignment location.
For example: person #1 may be assigned to School X for the year. They may have the following assignments: school principal (location: school X), PTA director (location: school X), student (location: local college), and member of board of directors (location: local hospital).
What is the best way to set up these tables and the forms to enter information into the tables?
Thanks so much for any help!
Hi, I'm trying to design a simple database with data entry via Data Access Page. I'd like to use radio buttons or check boxes (true/false) that, when selected, automatically insert on integer from 1 - 7 into the appropriate field.
Anyone have an idea as to how I should proceed. Designing the DB is not a problem, although I'm not sure of any DAP specific properties I need to set.
Thanks a lot...
I have noticed some recent threads on questionnaires and how to implement them through Access. I am fairly versed in normalization. However, I have ran into a snag in starting my questionnaire database.
I need to track answers from a survey that tracks consumer confidence. My business call consumers and goes through a questionnaire of roughly fifty questions. Some of these questions are yes/no, some are multiple choice and some are free text.
I have looked at some examples on this site to see what I may be able to accomplish but most seem very complex with append queries and the like.
I have attached a database the I have started with the help of an example from a post by, I believe, Jack Cowley. My questions are numerous.
1. How do I concentrate all the info from the tables on one form so that the user can view all the consumer's information?
2. Is it possible to not have to cycle through each question individually and list all the questions on the same form all at once?
3. In my tblAnswer, should I add another field to describe what sort of answer it is? Like if it is multiple choice, yes/no, etc?
How good is access for creating a short online questionnaire?
Hi there, im pretty new to Access and dont have a lot of database experience really, ive been studying access for the past couple of days trying to figure out how to create a questionnaire database that i need quite quickly. I've been searching the net looking for help on creating this particular type of database in Access for a while but so far have had no luck. Most of the questions in the questionnaire are just lookup tables to allow the user to choose a single answer from a list. But my problem is that some other questions can have more than one answer, for this type of question ive just used like 4 or 5 combo boxes to let the user tick things that apply to them.
At the moment I have a table like this structure..
FIELD NAME --------- DATATYPE--------------DESCRIPTION
RespondantID ------ Autonumber
Male or Female ----- Text (Lookup list) ------ Question 1
Age group ----------- Text (Lookup list) ------ Question 2
Dyslexia --------------- Yes/No (Combo box)-- Question 3
Deaf ------------------- Yes/No (Combo box)-- Question 3
Wheelchair user ------ Yes/No (Combo box)-- Question 3
Learning Difficulity --- Yes/No (Combo box)-- Question 3
Other ------------------- Memo ------------------ Quesiton 3
Internet Experience -- Text (Lookup list) ----- Question 4
How often used ------- Text (Lookup list) ----- Question 5
Facilities Nearby ------- Text (Lookup list) ----- Question 6
At the moment its not a relational database, there is only one table like the one above that has a corresponding form to allow to user to start populating it with info, i have read in a few other examples for questionnaires that multiple tables are a better option, but im really not certain about the structure needed for this type of database. Once its completed i will also need to be able to produce graphs (pie charts specifically) to show quite specific info, say for instance what percentage of users ticked that they are dyslexic and ALSO a wheelchair user.
One problem ive noticed with just having a table like this is that its fairly easy for me to create a pie chart from values taken from the lookup tables, but ive no idea how to go about creating one from just ticked check boxes.
Can anyone help out? Or ideally attach a small example database with just a couple of questions showing the correct structure to use for this particular type of thing, which i could then flesh out? Im a newbie thats really stuck. : /
Many thanks for any help offered.
Edited by: fade on Sun Dec 18 9:30:33 EST 2005.
Edited by: fade on Sun Dec 18 9:31:52 EST 2005.
Hi everyone, hope you are well
I am creating a questionnaire DB for employees to grade their immediate managers. I have the following table structure:
I have looked at some of the posts on here and seen the sample database. However, I have attached a pic of how I would need the form to look and I'm not sure how to achieve this with my structure. I'm wanting it so that I can amend the questions (the answers will stay the same) without having to amend the front end (i.e. just changing the linked back end table of questions will update the form).
Does anyone know how to go about this? I know I haven't got a responses table yet and that I should get the table structure right before I think about the forms/reports etc, but i didn't know if my table structure would support what I need so thought I best ask first off.
Hopefully that makes sense and someone will be able to help
Merry christmas and yuletide greeting to everyone as well!!
I am combining 12 Databases. I have split all 12 between the server and the desktop. My question is “Should I combing all of the data table on the server into one database or should I leave all of the individual application data tables in separate Databases on the server”. Additionally, is there a limit to the number of tables an Access DB can handle? My inclination is to keep the functionality separated but the problem I have is that some of the functionality within the applications overlap. Recommendations!
I have sort of problem - I'm trying to desing the database for one of the organisations.
The entities are: Family, Child, Referral, Voucher, Receipt, Provider, Activity.
That's ok so far. I've been asked to do the option for the new address of the Child - in case if they move to the new place and keep the old one as well.
Family table is the 'child table type' for the Child table - family can have more than one child, but one child can be part of only one family (one to many relationship).
I thought to make a seperate table 'tblAddress' and store all addresses in this table - then link them to the family - this however requires from me to have additional field for - for instance - second address, third address, which in case there was no change in address would remain empty value.
The other option - which obviously is absolutely waste of space is to add straight away additional address fields in the 'Family' table itself.
Don't you know of any other, more appropriate solution?
If you need more detail - I can send the copy of the database which I have created so far.
I would appreciate any help.
To all you access Gurus
I have been asked to look at setting up and access database to do the following
To keep a record of pupils and 9 tasks the pupils have to do and also the total amount of time it took a pupil to do this task
They have a total of 25 Hours to do all tasks but this is spread out over the year.
What I need is someone to tell me the best table layout to do this
The pupil record has to have the following
Total Hours taken for all 9 Objectives
A description of what the pupil did to achive each Objectives
record if that task is complete
Also Date Task was Complete
I have done a test database with just one table in it and inside that table
had all of the above but apart from name, form and total hours I repeated the rest nine times.
This just does not seem right
What I want is to pull up the pupil name and then select Task say from a Drop down box this would then insert a new field if it did not allready exist in pupil recored and then you can fill in the task details of course if the field/s all ready existed then to open that up to allow you to update this
It would then need to update the total Hours field in the pupil record with the hours it took to do that task/objective.
I have basic Access knowledge but i cannot think of best way to achive this can anyone help please
HILBRE HIGH SCHOOL
I'm a novice Access user who has been asked to create an RFP Database. My company responds to alot of RFP/RFI's and they would a like a database to track Questions and Answers.
Seems straightforward I think? However, my first table looks "too" basic
Any advice from those who have built similar DB's would be much appreciated.
New to forum. Beginner/Intermediate Access Experience.
Here is what I am trying to accomplish:
The database i am creating will basically need to have a user check-off a list of Fire Extinguishers that he has checked, on a monthly basis. For example, the user will enter a date, and then a form with a list of all the extinguishers will pop up, and he will place a check mark by each one. When he enters the check mark, that date will be stored so that we have a history of when each extinguisher was checked and by whom (using initials or something)
Currently, my design is simple, something like this:
My question is this:
-Am I going down the right track with the 2 tables?
-Do I need a third table to Store the Historical Data?
-I am not quite sure how to layout the form so that all the extinguishers are listed.
I know these questions may seem vague, but any help would by highly appreciated.
I am trying to set up a database that manages responses to a variety of survey questionaires, and I want to know if I am on the right track before I proceed. Sorry if this gets a bit verbose:
Here's the general 'business rules':
1) There are many different historic survey types. Some have also not yet been defined.
2) A specific question may appear on more than one survey type.
3) Each survey type can have a different number of questions.
4) The 'valid' answers to a survey question fall into two general groupings:
- multiple choice (variable number of choices)
- freeform text (and "other__________ " could be a multiple choice response)
5) There will be a lot of repetition in the multiple-choice choices. For example, there will be lots of "TRUE/FALSE" or "YES/NO" questions, lots of 'Rate the following on a scale of 1 to 5' type questions, etc.
6) Some of the multiple choice valid-answer-sets may be more unique, such as '200,400,600,1000,other'.
7) We (sometimes) want to capture such information as 'don't know', or 'answer illegible' or 'not answered' or 'don't care', but these can just be more 'pre-set' choices in the db that do not appear on the paper forms
After some whiteboarding, I arrived at the attached db structure....