UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Multiple Surveys And Their Tables And Relationships, Access 2016    
 
   
Pondering
post Apr 1 2019, 11:49 AM
Post#1



Posts: 3
Joined: 29-March 19



I've been tasked with building a database to enter data and begin analysis on over 10 years worth of surveys and program evaluations that have been collected on program for which I work. All of the data is currently on individual paper survey instruments.

I'm struggling with building relationships for multiple tables that will contain the results of different survey data.

I have searched the forum and have looked at the following posts, but remain confused.

I've looked at Duane Hookam's At Your Survey

I've also looked at NoahP's https://www.UtterAccess.com/forum/index.php...p;#entry1071547

One aspect I'm dealing with is the eventual need to develop data entry forms that look as much as possible like the paper forms to help reduce data entry errors by low-skilled data entry operators.

Before I found the survey database examples note above, here is the direction I was headed:

tblClass has multiple students with one to many link to tblStudentDemog. In this tblCLass, "Class" refers to the students' graduating year. It has the PK ClassID and is linked to the FK in tblStudentDemog.

tblStudentDemog contains the names and demographic data of the students. Its PK is StudentID and is linked to the FK in tblPrograms. It has the PK ProgramID and contains a FK LookupProgramNameID

tblLookupProgram contains a list of the 8 different programs students might participate in. They will participate in at least 4 programs and could vary beyond that number up to a total of 8. The PK LookupProgramNameID links back to the FK in tbl Programs.

At this point, I'm not sure how to proceed with building relationships and tables.

Each program will have several survey instruments that are administered to the students.

Three mockups are shown below.

How do I build the relationships so I can have pre/post tests, program evaluations, opinion survey data, etc. while linking the student demographic data?

Once the relationships and tables are built, I'll need to build a data entry form as everything is currently on paper.

Thanks in advance for any guidance provided!

Here are the layouts of the tables:

tblClass
--------
ClassID (PK)
Class

tblStudentDemog
---------------
StudentID (PK)
FirstName
MiddleName
LastName
PreferredName
Gender
DOB
Race
Ethnicity
ClassID (FK)

tlbPrograms
-----------
ProgramID (PK)
LookupProgramNameID (FK)
ProgramYear
StudentID (FK)
Survey ID (FK)

tblLookupProgram
----------------
LookupProgramNameID (PK)
ProgramName

tblPreTest
----------
PreTestID (PK)
PretestQ1
PretestQ2
PretestQ3

tblPostTest
-----------
PostTestID (PK)
PostTestQ1
PostTestQ2
PostTestQ3

tblEvaluation
-------------
EvaluationID (PK)
EvalQ1
EvalQ2
EvalQ3
Go to the top of the page
 
MadPiet
post Apr 1 2019, 12:02 PM
Post#2



Posts: 3,131
Joined: 27-February 09



Wish I could find Keri Hardwick's article on creating a proper survey in Access from 20+ years ago.

Something like

Question (QuestionID, QuestionText)
AnswerChoice(AnswerChoiceID, QuestionID, AnswerText)
Answer (SurveyID, QuestionID, AnswerID)

In a word, that repeating field (they all end in numbers) is going to bite you later on.
I've been bitten my someone else's design that I inherited that used the design you are suggesting. I had to write dynamic SQL to fix it. Don't go there. You're making your life way harder than it needs to be.
This post has been edited by MadPiet: Apr 1 2019, 12:35 PM
Go to the top of the page
 
tina t
post Apr 1 2019, 12:02 PM
Post#3



Posts: 5,956
Joined: 11-November 10
From: SoCal, USA


nope, those are flat-file tables - like you'd build in Excel. a flat-file design is easy to pick up on, because you're storing data in fieldnames - repeating groups of the same one or more fields which are typically numbered, as Q1, Q2, Q3, etc. and you're storing data in table names as well, by separating examples of the same entity into separate tables - ThisSurvey, ThatSurvey, AnotherSurvey.

take another, close, look at Duane Hookam's AtYourSurvey demo. it's classic relational design, with all data stored as data in fields, with infinite expandability - don't make another survey table, just add another survery in the setup tables, as records (data). don't add another question/answer field, add another question/answer record (data) in the existing tables setup. compare Duane's demo to your tables setup, so you can see where you need to improve your design.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
GroverParkGeorge
post Apr 1 2019, 12:17 PM
Post#4


UA Admin
Posts: 35,145
Joined: 20-June 02
From: Newcastle, WA


Duane's sample mdb (I don't know if he updated it to accdb, but it works still) does pretty much the equivalent of that, as tina pointed out.

And yes, a "spreadsheet style" table with repeating groups is NOT a good idea. I have a couple of YouTube videos that might be relevant. Here, and more importantly, here. There are additional installments that expand on the ideas.


--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Pondering
post Apr 1 2019, 01:11 PM
Post#5



Posts: 3
Joined: 29-March 19



Thanks to all for the resources. I'll dive back into Duane Hookham's database and study the video resouces.

Meanwhile: so, even though I may have several different surveys, each with different questions, all of the questions will go into the questions table with their unique ID? Rinse and repeat for AnswerChoice and Answer tables?
This post has been edited by Pondering: Apr 1 2019, 01:14 PM
Go to the top of the page
 
MadPiet
post Apr 1 2019, 02:21 PM
Post#6



Posts: 3,131
Joined: 27-February 09



IF you have a bunch of questions that can show up in many Surveys, then you could have

Survey--(1,M)--SurveyQuestion--(M,1)--Question--(1,M)--AnswerChoice
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th June 2019 - 01:05 PM