[AccessD] Question of Approach

No, you'd want something like:

tblClient - One record per client
ClientID - Autonumber - PK
Fname - Text
Lname - Text
DOB - D/T

tblSurveys - One record per survey
SurveyID - Autonumber - PK
StartAge - Long
EndAge - Long

tblQuestions - One record per question
QuestionID - Autonumber - PK
Question - Text
AnswerType - Integer

tblSurveyQuestions - one record per survey per question
SurveyQuestionID - Autonumber - PK
SurveyID - Long - Foreign Key to tblSurveys - CK1A, CK2A
QuestionID - Long - Foreign Key to tblQuestions - CK1B
SequenceNumber - Long - CK2B

tblClientSurveys - One record per client per survey taken
ClientSurveyID - Autonumber - PK 
ClientID - Long - Foreign Key to tblClients
SurveyID - Long - Foreign Key to tblSurveys
DateTaken - D/T

tblClientSurveyAnswers - one record per client survey per question
ClientSurveyAnswersID - Autonumber - PK
ClientSurveyID - Long - Foreign Key to tblClientSurveys
SurveyQuestionID - Long - Foreign Key to tblSurveyQuestions
Response - Text

 This would allow any number of surveys, with any number of questions, and
allow a client to take a survey more then once.

Jim.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John Clark
Sent: Wednesday, January 29, 2014 02:05 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Question of Approach

Alright...I don't know why I'm having such an issue w/this, but my brain is
fried and I'm 2nd guessing everything now...
 
I'm going to try to simplify and illustrate this issue, and then see how
y'all might do it...
 
Let's say I have 4 tables...there will be "support" tables as well, but I
think we can forget about these for now. Here are the 4 tables, their
fields, and a brief explanation, if needed (some are obvious), for them...
 
Table 1 = tblClient ... a list of ALL clients that come into the clinic
 
txtLName 
txtFName
txtMI
txtDoB
txtIdentifier ...a "client ID" derived from name and DoB...mine is
"CLAJOW19660903"
 
Table 2 = tblSurvey1 
 
ID ... an autonumber ID to give each survey an ID
lnkClient ...a link to the client...probably using txtIdentifier
datDoS ...Date of the survey
txtSurv1A
logSurv1B
numSurv1C
 
Table 3 = tblSurvey2
 
ID ... an autonumber ID to give each survey an ID
lnkClient ...a link to the client...probably using txtIdentifier
datDoS ...Date of the survey
txtSurv2A
logSurv2B
numSurv2C
 
Table 4 = tblSurvey3
 
ID ... an autonumber ID to give each survey an ID
lnkClient ...a link to the client...probably using txtIdentifier
datDoS ...Date of the survey
txtSurv3A
logSurv3B
numSurv3C
 
*** the 3 similar fields are just simplified place holders, representing
these surveys ***
 
Now, let's assume...because it IS the case...that each survey is based on a
clients age. And, that they can have multiples of each survey. To give a
scenario...and again, simplify things...let's say that the surveys cover yrs
1-5, 6-10, and 11-15, in that order (Surveys 1-3). We'll assume they do a
survey every year, so there will be multiples of each...a single client
should have 15 surveys, of 3 types, over 15 years. 
 
Now WHY can I not get this?! 
 
I would love to simplify the entry process, by only presenting them with the
proper survey. So, I'm thinking...and this is why I do now...that we need to
first put in client info (i.e. name and DoB), and this, along w/the date of
the survey, will give you their age, and thus the proper survey...???
 
What is it that is giving me this mental block? Am I being dense, or is
there really something tricky going on here?

Notice: This electronic transmission is intended for the sole use of the
individual or entity to which it is addressed and may contain confidential,
privileged or otherwise legally protected information. If you are not the
intended recipient, or if you believe you are not the intended recipient,
you are hereby notified that any use, disclosure, copying, distribution, or
the taking of any action in reliance on the contents of this information, is
strictly prohibited. Niagara County is not responsible for the content of
any external hyperlink referenced in this email or any email. 
IF YOU HAVE RECEIVED THIS TRANSMISSION IN ERROR, PLEASE NOTIFY THE SENDER
IMMEDIATELY BY EMAIL AND DELETE THE ORIGINAL MESSAGE ALONG WITH ANY PAPER OR
ELECTRONIC COPIES. 
Thank you for your cooperation.