Survey Table Design, Have Searched Require Clarification.
Jan 12, 2007
Firstly hello, I'm new! :)
Sorry for creating yet another thread on table design/relationships for surveys, I did search just needed a bit of clarification.
From what I understand from my search the best way to create a basic db for surveys would be a question table, response options table, and response table, this is similar to a database I have implemented for a simple online surveying system. Now this works with Y/N, or even limited response questions.
However I can't get it to fit with my current project, and I don't think it will, but I thought I'd check to see if I'm being stupid.
I have a survey, 20ish questions expecting 200-250 responses.
The questions consist of a mixture of:
Yes/No answers
Select all that apply answers
Select one from list
Select one from list or select Other and enter it yourself
Text/Comments
Because of the wide range of values, some completely unique and the possible number of answers per question per survey it seems the above method (and that implemented in At Your Survey which i've had a look at) would not work.
I only need to store responses (not questions), so I'm proposing a main table with related tables for those questions that require it.
So in essence a flat file response table with each field being a different question, each record being a separate survey response.
For those questions with multiple answers a separate table, related to the main response response table.
Data entry will be twofold, a web version of the survey automatically recording responses and manual entry into custom form within access for those paper returns.
Reporting will be faily widespread with a lot of cross referencing.
I know this is not a reusable format, and it is pretty much a one time only project anyway, relatively short lived. But I'd still like to create the most efficient product I can, within the time constraints.
Any views or help would be much appreciated :)
View Replies
ADVERTISEMENT
Aug 25, 2005
Hey all, I'm trying to design a Test / Survey database.
Please look at the .jpg attached or download the sample database.
I have looked at some previous survey/tests design posted on this forum and had a good table design based off of a Pat Hartman suggestion. Now i am wanting more out of the database and need more advanced options.
Here is what i am trying to do. When a user decides to make a new question for a test/survey they will have to choose what type of question the question will be. For example say the user wants to make a answer grid question. They would choose Answer Grid from the tblquestionstypes combobox. Then they would have the option of choosing a type of answer grid (Dual Scale, Rating Choice, Ranking Choice) from the tblQuestionOptionTypes combo box. Say the user chose Ranking Choice then they would be presented with a list of options for the Ranking Choice. For example 5 sets of rankings, 4 sets of rankings. 3 sets of rankings. (These are just coming off of the top of my head but i think you know what i mean.)
Question, Is this close to being correct. mind you i have just started this and am just throwing ideas around.
Where could i store the correct answer for each question. Sometimes there will be a answer and sometimes there will not be an answer due to the fact that it could be a test or a survey.
Any help or suggestions would be appreciated. Thanks
View 4 Replies
View Related
Oct 14, 2005
Hi, All--
I am designing a database to capture the data of returned surveys. I want to design the database to facilitate data analysis through crosstabs or other aggregation queries.
If I design a table where each record is the complete survey responses to all survey items in a returned survey, this is not friendly for such query analysis. (In this, each field would be a survey item). Call this the horizontal method.
The other way would be to have a reference table containing the survey items , and have responses entered in a seperate table linked by item id and response id (from a third table containing a record for each submitted survey). Call this the vertical method. This would take more time to set up but would probably be easier to query.
The item response table would become quite long contaiging every item response for every survey turned although each record is short.
Does anyone have any opinion on this, or perhaps a completely different approach that I haven't thought of that would be easy to set up but also easy to query?
Thanks.
Paul
View 2 Replies
View Related
Nov 21, 2007
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....
View 4 Replies
View Related
Jun 20, 2015
I am trying to make an amend record form which gets values from a subform. Most of the values that I need to amend on the form do amend. My problem is that I need to amend a value that is also used to find the record.
The value in the Table(BookInTable) that I am trying to amend is Barcode which is a text value. I have been trying to pass the initial value into a string(Bar) and have been using an SQL Update string.
Code:
Private Sub Command23_Click()
Dim Bar As String
Bar = Forms!FrmAmendOrder.AmendOrderSubform!BarCode
If IsNull(Me![POTxt]) Or (Me![POTxt]) = "" Then
MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
Me![POTxt].SetFocus
[Code] .....
BarTxt and POTxt are textboxes that show the initial values and BarTxt is editable.
By adding watches I can see that when I click the button Command23 Bar = the initial value and BarTxt.value = the edited value. But when I look at the table nothing has changed.
View 3 Replies
View Related
Jul 16, 2007
Hi, I have a survey database file containing the following tables
tblQuestions - contains survey questions
tblResponse - contains responses inputted by user
tblRespondents - contains info on user (e.g. Name, Add...etc)
Is it possible that answer to some questions are dependent on other questions?
for example:
Question 1 -True/false
Question 2 -True/false
Question 3 -True/false
If question1=false or question2=false then question3=false
(the response to question 3 is automatic and dependent on the responses of question1 and question2)
Thanks in advance for those who can give me some suggestions.
View 3 Replies
View Related
Oct 29, 2004
I am using Access 2002 database with ASP.NET(VB). I usually try to get something working in the access 2002 GUI 1st and I am having trouble with creating the code to require that a field entry be forced to be another exisiting entry in another table. For Example:
UNIT_TABLE has fields UNIT_ID(key), and UNIT_NAME having values of:
1 Tigers
2 Bears
3 Sparrows
BATTLES_TABLE has fields BATTLES_ID(key), ATTACKER_MAIN, and DEFENDER_MAIN
I want the ATTACKER_MAIN and DEFENDER_MAIN choices to ONLY be members from UNIT_TABLE.UNIT_NAME
I set up a combobox and lookup in BATTLES_TABLE as follows:
SELECT UNIT_TABLE.UNIT_NAME FROM UNIT_TABLE ORDER BY UNIT_TABLE.UNIT_NAME;
this OFFERS the CHOICE of any of the 3 clans (ie Tigers, Bears, Sparrows)
but I cannot not FORCE the USE of these....because the end user can option to enter in anything else.(which is not wanted).
How do I add SQL to the LOOKUP such that... the user can ONLY choose to enter text that EXACTLY matches an Already existing entry in the UNIT_NAME field?
I tried to enter a validation rule:
[ATTACKER_MAIN] In ([UNIT_TABLE.UNIT_NAME])
I get an error that the field 'UNIT_TABLE.UNIT_NAME' does not exist.
Thanks for any help with this validation problem.
HKP_MACK
View 3 Replies
View Related
Sep 19, 2005
Good morning,
I am having problems figuring out how to set up my table/query structure for a series of surveys that are based on specific user groups. In a nutshell how do I set up the relationships for the Questions, Employees, and Answers if I have the following tables (simplified):
tblQuestions
--------------------
QuestionID (P)
UserGroupID
Question
tblEmployees
--------------------
EmployeeID (P)
Name
UserGroupID
tblAnswers
--------------------
AnswerID (P)
QuestionID
EmployeeID
Answer
The functionality requirements are:
1. Create a set of questions for two or more user groups (each set contain different questions).
2. Assign Employees to a specific user group.
3. Employees are able to enter the DB to answer the questions within their user group.
What should happen is the employee accesses their set of usergroup questions and enter the corresponding answers. Each time I try to set up the relationships however, the answer field ends up being uneditable.
Any help you can give would save what little hair I have left.
Thanks!
Bob
View 2 Replies
View Related
Sep 6, 2014
I have a project that in Access. I'll be handling a survey file that will maintain answers per customer. The visit to customer is done monthly in a year, which means that a customer can have 12 visits in a year. My problem is that if I will maintain this project for the next five years or more, I have to maintain another field in my table that will block the duplicate entries.
View 5 Replies
View Related
Apr 7, 2007
When I made my Database, my Access Objects window displayed only the tables I created apart from the default Create table in design view, Create table using wizard and Create table by entering data. After finalising the database, I transferrred it to another PC and made some changes. The changed database was copied in pendrive and put in my harddrive. When I opened the DB, I find the following additional things in the Objects window :
MySysAccessObjects
MySysAccessXML
MySysACEs
MySysObjects
MySysQueriesMySysRelationships
How did this happen? It cannot be deleted. What is this? Can anyone explain?
Thanks in advance.
View 4 Replies
View Related
Sep 19, 2007
Hi,
I have a bit of a big query and dont really know what some aspects of it are doing. Can someone tell me why there are so many group by functions? Does there need to be one for every FIRST in the query? If so, what does this accomplish? Also tmp_recdata_trades has around 20,000 records in it. And the query returns one record? Why just one? And its a record in the middle of the table somewhere so there must be something special about it?
SELECT DISTINCT tmp_recdata_trades.[Client SPN Number] AS spn_id, First(tmp_recdata_trades.[Client SPN Name]) AS spn_name, First(tmp_recdata_trades.[Revenue Event Day]) AS trade_date, First(tmp_recdata_trades.[Product Name - Level 4]) AS product_name, First(tmp_recdata_trades.[Client Domicile Name - Level 0]) AS client_domicile_name, First(tmp_recdata_trades.[Team Name - Level 1]) AS team_name, First(tmp_recdata_trades.[Person Name]) AS person_name, First(tmp_recdata_trades.[Source System Name]) AS source_system_name, First(tmp_recdata_trades.[Business Hierarchy Name - Level 1]) AS business_hierarchy_name, First(tmp_recdata_trades.[Client Role]) AS client_role, First(tmp_recdata_trades.[Client Sector Name - Level 3]) AS client_sector_name, tmp_recdata_trades.dox_fsa, tmp_recdata_trades.dox_aml, tmp_recdata_trades.dox_aof, tmp_recdata_trades.dox_sub_acct, tmp_recdata_trades.notes_aof_live, tmp_recdata_trades.notes_aof_archive, tmp_recdata_trades.remediation_project, tmp_recdata_trades.remediation, tmp_recdata_trades.custody, tmp_recdata_trades.pb, tmp_recdata_trades.xbus_signoff, tmp_recdata_trades.wss_signoff, tmp_recdata_trades.man_approved, tmp_recdata_trades.fsa_class, tmp_recdata_trades.opted_up
FROM tmp_recdata_trades
GROUP BY tmp_recdata_trades.[Client SPN Number], tmp_recdata_trades.dox_fsa, tmp_recdata_trades.dox_aml, tmp_recdata_trades.dox_aof, tmp_recdata_trades.dox_sub_acct, tmp_recdata_trades.notes_aof_live, tmp_recdata_trades.notes_aof_archive, tmp_recdata_trades.remediation_project, tmp_recdata_trades.remediation, tmp_recdata_trades.custody, tmp_recdata_trades.pb, tmp_recdata_trades.xbus_signoff, tmp_recdata_trades.wss_signoff, tmp_recdata_trades.man_approved, tmp_recdata_trades.fsa_class, tmp_recdata_trades.opted_up;
View 1 Replies
View Related
Nov 12, 2014
I would like to clarify the concept of record-level locking Say I have a TBLA which consists of two fields TBLA_FLD1 & TBLA_FLD2...TBLB which consists of three fields TBLB_FLD1 & TBLB_FLD2 & TBLB_FLD3
Now two users have declared the usage of the database as shared and pessimistic...I have generated a query in addition to TBLA & TBLB which is QryCombined which basically just a join of the two and consists of all five fields...If user1 has brought in a record query from QryCombined and edits on TBLA_FLD1 & TBLB_FLD3...At the same time user2 is trying to bring in as well QryCombined but with TBLA_FLD2 & TBLB_FLD1 and trying to edit both, will user2 be blocked by user1 who's editing the other fields
For all I know, the locking is based on record-level locking so I would suppose one record of QryCombined will comprise of all five fields so editing in any of them should actually block another user from editing. However I am not sure whether it is just a subset of the record that is the contesting area because user1 recordset has only two fields out of five, which do not happen to intercept the other fields that user2 is working on.
View 3 Replies
View Related
Aug 8, 2007
In one record (or row?) there I have 10 fields (columns), how can i find the standard deviation of those 10 numbers. Seems to only want to be able to do standard deviation if all data is in one field or column. I tried doing a query but same thing, if all the fields are across, it doesatn find the standard deviation of them. Please help this worthless access user. Thank you, I look foward to all the help I can get. As you can see, I still am struggling with even terminology. Excel is easy to me, access is a challenge to say the least.
View 3 Replies
View Related
Sep 27, 2005
I have used the search and it proved to be very helpful, as always. However, I still have an issue with my coding, it keeps trying to open my Excel file I have imported after I have closed it (or at least I think I have). Here is the code (copied right from another thread with a few tweaks):
Option Explicit
Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long
Private Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type
Private Sub Command0_Click()
Dim OpenFile As OPENFILENAME
Dim lReturn As Long
Dim sFilter As String
Dim WrksheetName As String
Dim oApp As Object
OpenFile.lStructSize = Len(OpenFile)
OpenFile.hwndOwner = Form.Hwnd
sFilter = "acSpreadsheetTypeExcel9 (*.xls)" & Chr(0) & "*.xls" & Chr(0)
OpenFile.lpstrFilter = sFilter
OpenFile.nFilterIndex = 1
OpenFile.lpstrFile = String(257, 0)
OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
OpenFile.lpstrFileTitle = OpenFile.lpstrFile
OpenFile.nMaxFileTitle = OpenFile.nMaxFile
OpenFile.lpstrInitialDir = "C:"
OpenFile.lpstrTitle = "Select the Information to Import"
OpenFile.flags = 0
lReturn = GetOpenFileName(OpenFile)
If lReturn = 0 Then
Exit Sub
End If
Set oApp = CreateObject("Excel.Application")
oApp.Visible = True
oApp.Workbooks.Open OpenFile.lpstrFile
With oApp
.Visible = True
WrksheetName = "Import"
DoCmd.TransferSpreadsheet (acImport), acSpreadsheetTypeExcel9, WrksheetName, OpenFile.lpstrFile, True
End With
oApp.Workbooks.Close
oApp.Quit
Set oApp = Nothing
End Sub
View 5 Replies
View Related
Dec 15, 2006
I did search the forums on this subject and can not figure out how to do what I want to do.
I have a form and subform setup. Basically by main form has contacts and my subform has log notes. It logs the date for every new record. It sorted fine up until yesterday when I ran an update query to change all the userIDs to UPPERCASE. Now it seems to be throwing the subforms in a random order.
I need the subform to order by Order date. Simple as that. I tried setting the property "Orderby" to Calls.Orderdate but it didn't work.
Thanks!
View 5 Replies
View Related
Dec 9, 2011
Is there a function, similar to mid, except that it will return all characters prior to the searched string instead of after?
View 4 Replies
View Related
Jul 25, 2014
I'm trying to catalog these shelves for work. So someone can then search for the item and it will pull up the shelf its on. The shelf has alot on it so I want to set the focus on the item on the shelf. But I'm not too sure how to do that with my search button.
This is the code for my search button:
Private Sub cmdsearch_Click()
'Purpose: Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both inclusive. _
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including this one).
[Code] .....
View 14 Replies
View Related
Oct 20, 2005
Hi,
I've been searching the forums, and I find a bunch of posts about the subject, but I just can't seem to really find what i need.
What I'm looking for is how to construct a query, that will exclude any entries in my table that might fall on a Saturday or Sunday.
What is the easiest way to do this?
I'm going to be using the results of this query in a Report and DAP.
Thanks
Chris.
View 3 Replies
View Related
Sep 28, 2006
Attached program needs little code correction; but, I do not know how, Any help will be highly appreciated
The code attached works fine with fields Dentist , patient number and name, but when I try to apply the same code at fields productID and Quantity does not work because these fields at sub form , Can any one help me to handle this problem please ?
All what I need to in force user to fill all fields ( all fields require ) dentist, patient number, patient name, product Id and Quantity,
Regards
vbnt
View 7 Replies
View Related
Nov 21, 2007
Hi,
a colleague of mine has Office 2000 installed on her work PC in order to carry out Access datadase responsibilities (amongst other things) as part of her job.
I think (though I cannot be sure) that someone has 'locked down' the copy of Access to such a point that shift bypass no longer works, F11 does not display the database window etc. This is strange because our company generally does a default install of Office and leaves most everything configured as standard.
In short, I need to access the Tools menu to set startup screen options and customize some of other options for her. Obviously I can't.
Will this problem require a complete Office re-install or is there a way to undo someone elses changes and set Access back to 'wide-open' ??
Thanks
Sausagefingers
View 5 Replies
View Related
Jun 6, 2014
In a table, I'd like to require an entry in either Field A or Field B. But an entry should be in only one of the two fields.
View 9 Replies
View Related
Aug 8, 2005
I haven't been able to figure out the code to require entry (for a new record) in a field on a subform - anyone can help with this?
The situation is a contacts database, and each new contact should have a (or multiple) Person Roles entered as well, which is their relationship to the organization.
View 2 Replies
View Related
Mar 14, 2012
I am trying to edit the following code to be able to require the user to enter a Loan number and keep prompting the user to enter a value as long as the field is blank. Once the field is filled in, then the code should go on to check if the sql condition is met and make the necessary change if met, then finally, I have a save command code that will require the user to save the record. Right now I am getting errors when I added the code to require the user to enter a field. I am new to loop and if statements in vba so I am not exactly sure how to structure it.
Code:
Private Sub Save_Record_Click()
Dim SQL As String
Do
If IsNull(Me![Loan Number]) Then MsgBox "Please Enter Loan Number. This is a required field."
[Code] ....
View 1 Replies
View Related
Sep 20, 2006
I've been asked to make a survey db by someone. I was wondering what is the best way to go about this? The output spread sheet list several questions asked of the user. The user id and questions asked are in columns. So you could have a columns like the user id: date: when was the last time you reaed a book?:Did you like the book?: Was the book helpful? What type of book was it?. These are listed in many rows one for each survey asked. Is my best bet to just make each column a field name in a table and build off of that? Some of the question asked are very length? I can have the spread sheet altered to column names that are more relivant and shorted so when I import into access and populate them into a table it will be easier to follow. Just looking for a little guidance. All thoughts are appreciated. Thanks...
View 2 Replies
View Related
Apr 13, 2006
I'm creating an employee survey database and I'm using option groups for collecting general information such as office, department, etc. My question is can I make it a requirement for them to answer an option group before moving on to other questions? I'm trying to control and track how many employees have taken the survey. Right now I can skip these general information questions and just go into the survey questions so I'm trying to prevent that. Also in the same train of thought is it possible to not save any incomplete surveys? Say I have 50 questions and someone starts the survey but only answers 10 questions and then exits for whatever reason. I would like that record to be deleted so it does not count. If I allow incomplete surveys then I can get skewed numbers.
As always you help is greatly appreciated.
Thanks,
Chester Campbell
View 7 Replies
View Related
Jul 26, 2007
I'm trying to create a validation rule that requires an input of 5 numeric characters OR if the entry begins with a letter it can be any length. For example if the record begins with a number the length must be 5. If it starts with a letter any length is acceptable.
Any help is appreciated!
Thank you!
View 2 Replies
View Related
