[AccessD] Question of Approach

It would seem to me that the easiest way is to use a client ID as a foreign
key in each of the survey tables.  

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John Clark
Sent: Wednesday, January 29, 2014 11:05 AM
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.