How do I show only aggregate data for survey responses?
Survey Database Design - hit a snag
I am working on a Survey / Questionnaire database and have hit a roadblock. I am trying to follow a design that mirrors the At Your Survey database in Roger's Access Library that has been recommended on these forums.
jpg (zipped since forum says new users can't post images) with the current relationship structure that I have. I have removed the Surveys table as there isn't a need formore than one survey any time soon.
Many of the survey questions are set up in sets like below (example only). Each question (Fruit 1-3) uses the same list of possible responses and the responses need to be ranked (Fruit 1= most preferred fruit, Fruit 3 = Least preferred).
List Your Top Three Fruits
Fruit 1: Apple
Fruit 2: Pear
Fruit 3: Orange
Because of this, I have set up an extra table with question categories. Otherwise, I would be storing a lot of redundant values if I had a table with only the QuestionID and PossibleResponses (some sets of questions will ask for as many as ten user responses that use the same set of possible responses)
I want to be able to join the Possible Responses table (SrvQPosResponses) to the table where the Survey Responses are stored (SrvResponses).
The At Your Survey database does not join these two. Don't I want to join them so that if I change a possible response, it will be updated in the survey responses as well (say if I want to rename Apple to Apples).
Comparing values from different records
I'm not sure exactly what is the best way to do this, so I have this under queries . I have a listing of survey responses from year A. I have another listing of survey responses from year B. Both are in the same table with a unique record - names and years are separate related tables. What I need to be able to do is find out if each individual's answers to questions X and Y changed from year A to year B and then count how many changed. In other words, I need a way to say that # individuals changed their answer in question X and Y from year A to B. Is there a way to query this
chart with multiple serieS
I am developing a survey application that has 4 questions with responses. the responses are strongly disagree, disagree, agree and strongly agree. I would like to make a chart of some type in access that would show the over breakdown of the questions and the total answers.
I was thinking something like a bar chart that would have the names of the questions along the x axis and a count of the responses along the y axis.
each question would have 4 bars one for each of the answers (strongly disagree, disagree, agree and disagree). the aim would be to allowmanagement to see where the strong points and weak points across the board.
Subract access Query
I have a database where, on a weekly basis, I will be entering around 20 lines. The columns are exported in from excel, with the date they were entered. A definition of the columns used are below.
1) SAN Name - The name of the SAN.
2) Aggregate Name - The name of the Aggregate, within the SAN.
3) Used - The amount of space used within the Aggregate
4) Total - The total amount of space in the Aggregate.
5) Date - The date the data was captured.
For example purposes, I currently have 3 sets of this data (03/01/2012, 09/01/2012, 16/01/2012). The only data that will change is the 'Used', 'Date' and possible the 'Total' columns.
What I would like is a query to run to show the 'different' between each SAN/Aggregate name (all the data will be held in the same table), so would expect to see an additional line everyadditional week there is data inputted; something like the below would be the ideal output.
SAN 1, Aggregate 1, Used -100, Total -0
SAN 1, Aggregate 2, Used 12, Total 50
SAN 2, Aggregate 1, Used -400, Total -0
Relational database concept example
And have been tasked with (what I hope to be) a fairly simple relational database problem. to get some feedback on whether or not this is a simple enough task for someone new to access, and 2. understand if this process seems correct.
I have data for 100 survey studies in excel that have already been broken down into 3 datasets. Study# uniquely identifies each survey, and is the same value across the 3 datasets. I washoping to be able to link all three data bases based on study#.
Below, I outline what is in each dataset, how I think I should be normalizing the data, and at the end describe how I think to combine the 3 data sets:
each study can have multiple responses for country, audience and database, In excel it would look something like:
A survey issue
I guess this is a pretty basic problem and I know when I hear the answer I will kick myself, but.
I am working on a survey form for my office. We have 5 departments, there are 12 questions that our customers are to answer with choices from a combo box,
My question is: Can I do a summary report by department which gives a count of the responses for each of the questions (ie: 5 Excellent, etc.)? I am nearlycertain it can be done, and I have tried several approaches, but I'm not getting the results I expect.
Question setting up tables for a survey
What is the best way to create tables for a survey. I have inherited a survey (in excel). I need to get it into Access. Unfortunately, there are more than 255 fields (more like 400). I've separated the survey into 6 tables.
The way my tables work, the ID is an autonumber. The next column is the survey number (since this cannot be duplicated). The student numbers are individual. The following can be duplicated: Date of Birth, Teacher name, Maiden name, Entry date.
How do I set up the relationship so that when someone enters new data in that the tables are linked?
There is no guarantee that whoever enters the data will not abandon data entry at any point during the data input.
Also, I am using Access 2007. Which of the following links should I be using if I wanted to use if I wanted to use Duane Hookom's "At Your Survey":
Hi; have searched and read forums for Questionnaire/Survey design,
I have one survey (which will not be repeated, and there will never be more than one survey). I am the only person who will input the data (from either web input form converted to csv or Excel, or from returned paper surveys).
I have not inputted any data, as I want to get the design correct/normalized etc before doing so.
I have set up tables according to info on these forums: ie separate tables with PK/FK etc for Respondents/Questions/etc etc. However, I am still confused about the Answers (Response Options) table; many questions are multiple choice, eg.
Yes/No; Strongly Agree to Strongly Disagree etc etc. But I don't know how to put this in a table, because although some questions have answers unique to them, some have answers in common with other questions (ie Yes/No). I also want to capture no responses.
Do I list each and every possible choice, somehow linking it to the question; eg Q1a 1 Yes, Q1b 2 No; OR do I make a different column for each type of response, and then use lookup tablesto reference, or an "Age Range" field?
Multiple entries wanted in Form
I am needing to create a form that will allow data entry of a survey. On this form I have set up survey date and location which are always unique to the survey. I also need to set up a way to enter an unknown number of survey results normally 1-5. The survey results are limited to defined results that are already in a table. I have set up a junction table between the survey table and the results table using each tables primary key. I am having no luck getting multiple entries of the results on the form.
Round robin survey design
I've got a survey that goes out to 4 people. Each survey is asking the people to rate aspects of the other 3. For instance an architect might be scoring a contractor in terms of health and safety.
There are 13 possible questions but not every question applies to every survey combination.
What I've done is to have a table with:
ResponseID - ProjectID - Name(person filling in survey) - ScoringWho (the role of the person they're scoring) - Q1 - Q2 - Q3 . Q13
As far as I can see the alternative is to have 1 row with every possible combination of response. But it means that every time data is entered 3 rows have to be filled. What's the better way to go about this?
Use Access 2007 Aggregate Functions
The Access 2007 aggregate functions calculate values over a range of data. The totals row is new with Access 2007 and makes it easier to use aggregate functions such as averages, counts, sums and totals. You may still use these functions in queries and Visual Basic Applications as before but the "Totals" row is generally faster. The following tutorial will show how the use the Access 2007 aggregate functions.
Query to populate data from rows to columns
I have been given survey data with questions in one column and answers in another column. Each survey has 7 questions and therefore each survey is listed on 7 rows like this.
1 Q1 D
1 Q2 S
1 Q3 N
1 Q4 VD
1 Q5 S
1 Q6 VS
1 Q7 Yes
2 Q1 VS
2 Q2 S
2 Q3 N
2 Q4 N
2 Q5 S
2 Q6 VS
2 Q7 No
I’ve been using Excel with named ranges and Vlookups to create a dataset with the 7 questions listed in separate columns with one survey per row like this (abbreviated to simplify example):
Survey Q1 Q2 Q3 Q4 Q5 Q6 Q7
1 | D | S | N VD S | VS Yes
2 | VS S | N N | S | VS No
so the Q1 column is the Answers to survey questions 1.
I am attempting to switch my process from Excel to Access 2007, but I'm having trouble getting results I need with one row per survey and answers to each survey question in a separate column.
I can get the results for answers to one question; however, I knew it wasn't that simple, but I don't know what I need to do. I think Ineed to create a new field with the expression in the field name, but I can’t figure out how to make it work.
Multi Column Combo Box on a form
I have an Access 2010 Database for a Survey our company is doing. We have a Call list table to generate the list of members to be called. We also have a results table where the survey results are stored.
The survey is on a Form with 5 Questions yes/no fields. 1 combo box for the type of survey being done and 1 combo box for the member to be called. The Row source from that combo box pulls a list of members from the call list table that have not been called yet.
The combo box has 5 fields: MemberID, lastname, firstname, middle initial, Phone number. the MemberID is the bound field and that is what is stored in the survey table.
What I noticed is when the user clicks on the arrow in the member combo box the 4 fields (name fields, phone number) are displayed once the name is selected the combo box only displays thelast name, nothing else is there any way to make the combo box display 4 fields even after selection so the user can see everything there while clicking on the members responses to thesurvey?
How can I add the same data into a batch of new records
I am trying to find a way that I can make filling in a number of forms a lot easier.
Basically I have a survey, which is completed and then inputted onto my database. There is some information to be inputted though which will be the same on every survey. For example, if thesurvey was taken on 12th April in the Market Square, the date of survey and location would be the same on survey.
So I am trying to find a way that I could have a form where I could specify the date and location. Then when I click add new records, I would go through each survey one by one but they would have the date and location information already inputted.
How would I go about doing this. I really can't think how I would even start doing this!
Running Sum Troubles
I have created a survey database and I created a report that shows all surveys for all scholars. Each survey has two different sections (section A and Section B).
So let’s say survey one has a total of 14 questions. Section A has 6 questions and sections B has 8 questions. I want to number each record per survey and then the numbering starts over again when there is a new student or a new survey. Example below:
Survey Database Table Design
I'm creating a survey database (Access 07). I have multiple tables and was wondering if you could help me with the design. Currently I have a tblQuestions & tblAnswers which contain only the questions themselves and the possible answers, respectively.
Would you recommend a 3rd table which ties the responses from the tblQuestions & tblAnswers together? Forsome reason I'm stumped by this and I'm sure it's a very easy solution.
Submitted via EggHeadCafe - Software Developer Portal of Choice Design Patterns for .NET
In part I of our case study, we examine one possible means of storing both single and multiple option form responses within the same data table.
Report that returns the percentage of complete vs. incomplete responses
The responses are captured via yes/no check boxes with a checkbox for YES, NO, and NA.
I'm trying to create a report that returns the percentage of complete vs. incomplete responses:
Q YES NO NA
1 100% 0% 50%
2 50% 50% 0%
3 NA NA 100%
4 100% 0% 0%
5 NA NA 100%
I can't figure out how to make the report generate "NA" for the YES and NO fields on questions who's responses are NA (questions 3 & 5 above).
I need to show questions that are NA without those #VALUE! deals all over the place.
How to: Use Aggregate Functions to Work with Values in Access SQL
Aggregate functions are used to calculate statistical and summary information from data in tables. Learn how to use aggregate functions in Access SQL.
Can someone please share a sample survey form in Access or recommend some online resources.
I need to design a form that will allow me to conduct a survey over the phone & record the answers.