[AccessD] Question of Approach

Contrary to Gary's approach;
I would have one table for the answers, or maybe one table for each of the
'age-group' surveys - where you add columns when new fields are required,
setting the entries in all new entries in existing entries to null when you
change the table.
That way the data can still be queried as a set.


As you may have multiple sets of answers for each age-group, maybe have a
date~timestamp on the answer sets so analysis can be done using a VIEW that
excludes any but the latest (max(date~timestamp)) set of each age set for each
client.
Maybe a 'deleted' marker and 2 timestamps - entry creation and last updated if
you are going to retain a history of entries/amendments
(never a good idea to physically delete entries, as there is always someone who
will select 'delete' by mistake)
You could use a sequential counter, to denote the latest entry but again that
requires extra work at data entry time.

VIEWS are good for user access to data as you can hide 'deleted' or superseded
entries, or fields from them, and substitute viewable data or comments for
entries that have inappropriate values in them.   
Also doing queries/reports based on views mean they still work when the base
data storage table gets changed.

While it may be 'nice' (especially for the users management)  to have the
questions available as a set of (user changeable) entries in a table that would
require you to parse the table to generate the form of questions - more work,
especially when the questions get longer and take up more space on the form.
Also embarrassing when the question that previously asked for age in years gets
changed to DOB - and the previous entries are not recalculated from the entries
timestamp and the earlier answer.
As a long term UAT I'll advise that Scrolling questions is not a good idea.

It may be a useful idea to have an additional table in the project detailing the
modifications - 
e.g. datestamp, authority requesting the change, authority to implement the
change, version including the change,  and a memo field detailing the change.

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

Having a structure where you would have a tables for

the client

the surveys

The questions

Survey question table (An intersection table between survey and questions)

Client survey answers (a transaction type table with the client, survey and
survey questions all linked)

is probably what would be best.

Will the survey's themselves change over time - questions added or removed
etc?  I would think so. And so having a fixed table for each survey is not
a good idea.

Good luck. As you say it's probably going to take less time to just rework
it from scratch than to get your head around what the original one was
doing. Treat it as a proof of concept and run with your own design. It may
end up looking similar to the users but inside it can be much different.

GK


On Wed, Jan 29, 2014 at 7:18 AM, John Clark <John.Clark at niagaracounty.com>wrote:

>
>
> >>> Gary Kjos <garykjos at gmail.com> 1/28/2014 3:20 PM >>>
> I'm assuming there can only be one of each age survey for each client? I
> would have a form for each survey I think.
>
> ***** No, I don't think so Gary. I'm not 100% positive, and I am currently
> waiting for an answer from this "client" regarding this. From our earlier
> conversations though, it seems that they can take a survey every year (I'm
> just throwing that time frame out there). This does make sense if you look
> at the ages for the surveys...the 2nd level survey is for kids 4-18.
>
> One of the options I had been contemplating is to combine the surveys and
> just enable/disable fields based on age. Some of the questions are the
> same, while others are more age relevant...for example the 1st survey (6 mo
> to 4 yrs) has questions regarding pacifiers and bottles.  There are only 42
> questions among the 3 (17, 12, 13), and several questions overlap...I just
> realized this morning, that surveys 2 & 3 only differ by maybe 2 questions.
>
>
> I would tend to think of subforms where you have multiple "child" records
> for a parent record so you can maintain those multiple child to parent
> connections. I would have a client entry form to enter/maintain the client
> info and then link to the survey form which would perhaps be a pop up form
> for each survey age group.
>
> ***** That is why I pointed her this way...multiple surveys for each
> client child.  But, something wasn't done right from the get-go, and w/the
> multiple surveys types...I've also started going back to scratch. I'm
> thinking, if I do it myself, from the top, then maybe I'll just do it
> correctly.
>
> But truth be told I'm way out of touch with actual ap development now a
> days. I just use Access as a query tool against Oracle databases with
> output going to Excel.
> ***** Yeah, my involvement w/Access WAS decreasing as well. But, it seems
> to be feast or famine for me here...I won't touch it much, if at all, for a
> year or so, and then I'll get a bunch of work regarding Access. Right now
> I'm going through a heavy Access period...it started at the beginning of
> the month, when I was called down to the Treasurers Dept because their
> "College Chargeback" program wasn't working. I spent a week on that, and as
> that began I was contacted by our Sheriff's dept to write a program for
> them...I haven't actually WRITTEN a program for a while, so I jumped at
> that one. Now, this issue popped up the end of last week, causing me to
> take a break from the Sheriff's program.
>
> Just before all this began, I was actually called down to the Real
> Property Dept, because their Tax Program wasn't working. This was Visual
> FoxPro, which made me cringe...I loved DOS FoxPro (2.6), but I fell away
> from it with Visual...just don't care for it. Anyhow, this wasn't so much a
> program issue, as it was a user issue...the person doing that job had
> recently retired. The new person really doesn't know what she should, to be
> doing the job. She was getting by, by just going to recent programs and
> projects run. When she could no longer use the previous user's Window's
> Profile that data was no longer available. I copied the profile data over
> to a new directory and it worked...they thought I was a genius...I was a
> little disguted, to be honest, that THIS person was going into a position
> they just are not qualified for. Oh well...that is government...
>
> I do know that you have to be comfortable with the design of what you are
> working on or you will be battling it over and over. So if you don't like
> it as a subform, make it to standalone forms.
>
> How is the database relationships. that is usually where newbe or user
> developed systems are weakest. You need to nail down the database structure
> correctly before you worry about the forms don't you?
>
> ***** To be honest, I just don't spend the time on relationships that I
> used to...and so far, I haven't suffered any ill effects, while I've saved
> a bunch of time.
>
>
-- 
Gary Kjos
garykjos at gmail.com
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com