Denormalizing for form only

[Amy Blankenship]

Hi, all;

Many times I've answered questions like this in the queries and tabledesign
forum:

I have a table like this:

MyThing
==========
MyThingID
MyThingDesc
MyThingField1
MyThingField2
MyThingField3


Now, how do I query in such a way that Field1, Field2, Field3 are all
treated in essentially the same way? And of course what I tell them is that
they need a separate table, with MyThingField1, MyThingField2, and
MyThingField3 as separate records.

But there is a reason people keep asking questions like this, and it is that
Access does a much better job of making it easy to make data entry forms
with the structure above than with the "correct" structure. With the
"correct" structure, you have to use a Left Join on some other table that
will return you 3 records, and then you have to worry about a frustrated
join. You also may have to add extra code to make sure that MyThingID gets
into the MyNewThing table as a FK. And even then, your users are dealing
with a vertical structure when the horizontal one might well be more user
friendly.

Or at least this is what I've always had to do.

I'm wondering if there isn't something I've been missing, some wonderfully
simple feature of Access, that makes using normalized data less painful from
a form-building point of view. It's very frustrating telling people "you
need to structure your data this way, but then once you do it you have to
manhandle Access into allowing you to enter data."

I'm thinking maybe it's something like PivotTable view, but so far I haven't
seen any tutorials that show how to use it for this.

TIA;

Amy

 

[Jeff Boyce]

Amy

Although the Excel-like Thing1, Thing2, Thing3 approach is familiar to Excel
users, as you already know, it isn't necessary (or desirable) to use this in
an Access database.

What I've found quite useful for such one-to-many relationship is a main
form/subform construction.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Amy Blankenship]

Amy

Although the Excel-like Thing1, Thing2, Thing3 approach is familiar to
Excel users, as you already know, it isn't necessary (or desirable) to use
this in an Access database.

What I've found quite useful for such one-to-many relationship is a main
form/subform construction.

So your position is to force the client to adapt to how Access does things,
rather than finding a way to force Access to present information in the way
your client finds easiest to work with. That's interesting, but it doesn't
really answer my question. My preference is to try were possible to do
things in a way that my client prefers.

 

[Bob Quintal]

So your position is to force the client to adapt to how Access
does things, rather than finding a way to force Access to present
information in the way your client finds easiest to work with.
That's interesting, but it doesn't really answer my question. My
preference is to try were possible to do things in a way that my
client prefers.

Amy,

you seem a little frustrated, but you are in error when you say
That's not the real reason, the truth is that until people are
familiar with the relational model, they tend to think "spreadsheet
on steroids", and build the database based on that presumption.

Yes, what you've been missing is using a proper subform with
properly defined relations between the tables and queries. Building
proper data input and data editing form/subform sets becomes child's
play once you learn how. The relational structure becomes
transparent to the client.

As to your preference in doing what your client prefers, I say it is
just lack of confidence in asserting that he will prefer it once
he's used to it.

 

[Amy Blankenship]

Amy,

you seem a little frustrated, but you are in error when you say

That's not the real reason, the truth is that until people are
familiar with the relational model, they tend to think "spreadsheet
on steroids", and build the database based on that presumption.

The truth is that spreadsheets are easier _for input_, and that is why new
designers tend to try to use it. Proper table design makes it way easier to
get data _out_ and is trivial to populate outside of Access, such as in a
web form, but Access really fights you on it.

Yes, what you've been missing is using a proper subform with
properly defined relations between the tables and queries. Building
proper data input and data editing form/subform sets becomes child's
play once you learn how. The relational structure becomes
transparent to the client.

I love the way that you presume that I don't know what I'm doing, and that
I'm asking from a point of complete ignorance. I am asking as someone who
has been working in Access for many years, and I was wondering if maybe
there wasn't some feature I'd missed that makes data entry into a normalized
table structure easy, since clients often don't want to pay for the hoops
you have to jump through to do it in Access--they simply don't realize how
difficult it is.

As to your preference in doing what your client prefers, I say it is
just lack of confidence in asserting that he will prefer it once
he's used to it.

OK, so tell me exactly how you'd make this structure easy to do data entry
on. This is a real world situation that I find myself up against, trying to
solve it in a way that isn't going to use hundreds of dollars worth of my
time. The situation is that of a SAT score conversion table. If the
subject matter is "writing" all scores must be indexed against the essay
score. If the subject matter is "reading" or "math", the score is not
indexed. So for any one given score, there can either be one data point, or
seven. It's much easier to keep mental track of the scores that index to a
particular "raw" score if you can enter them all in a row, across. At a
minimum, it means you don't have to enter the same raw score seven times.
Each practice SAT exam can have its own score conversion table for math,
reading, and writing.

The table structure is this:

ScoreSet
===========
ScoreSetId-Autonumber, PK
SubjectName-reading, writing, math
ScoreSetDesc-will allow users to select this set later and associate it with
an exam

ScoreSetItems
============
ScoreID-Autonumber PK
ScoreSetID-FK to scoreset
WritingScore-score to index this on (will be 0 for subjects that don't
apply, 0-6 for writing)
RawScore-the actual score on the multiple choice questions
SATScore-the scaled SAT score in the given subject

Your task, should you choose to accept it, is to create a form structure
that uses no code and no left joins and is not a royal pain in the butt to
enter all 7 data points for scores from -12 to 49 (434 data points PER exam
just for writing, plus the 65-90 data points for the ones that are not as
complicated) and that will be easy to use and intuitive.

Maybe that will explain to me what I've missed, and how this is so easy ;-).

-Amy

 

[Tom Wickerath]

Amy,

I certainly understood what you meant the first time. The two solutions you
were offered were vertical entry-based, which is counter to what you stated
that you wanted. I too have been frustrated at times by this limitation, and
in at least one case, I've intentionally denormalized some to accomodate this
limitation. In this case, the customer wanted a spreadsheet-like view of the
data (simple enough with a crosstab result), however, the data also had to be
editable.

I can tell you that lots of people have requested this in the past to
Microsoft. I just did a few weeks ago, and my contact at Microsoft replied
that they have heard this request many times. I'm not positive, but I think
one can accomodate this type of data entry with an ActiveX grid control, but
that introduces issues related to using ActiveX controls (distribution,
licensing, proper registration, etc.).


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html

 

[Thomas Lake]

Hi, all;

Many times I've answered questions like this in the queries and
tabledesign forum:

I have a table like this:

MyThing
==========
MyThingID
MyThingDesc
MyThingField1
MyThingField2
MyThingField3


Now, how do I query in such a way that Field1, Field2, Field3 are all
treated in essentially the same way? And of course what I tell them is
that they need a separate table, with MyThingField1, MyThingField2, and
MyThingField3 as separate records.

But there is a reason people keep asking questions like this, and it is
that Access does a much better job of making it easy to make data entry
forms with the structure above than with the "correct" structure. With
the "correct" structure, you have to use a Left Join on some other table
that will return you 3 records, and then you have to worry about a
frustrated join. You also may have to add extra code to make sure that
MyThingID gets into the MyNewThing table as a FK. And even then, your
users are dealing with a vertical structure when the horizontal one might
well be more user friendly.

Why limit yourself to Access input only? If users are more comfortable
with Excel-type input, let them use that. Then analyze the data in Access.

The whole idea behind Microsoft Office is to be able to use a suite of
programs that interoperate. You can have the front end be an Excel
worksheet while the back end is Access. I've also seen Word used to
create forms that created a data file for Excel or Access. Just as a
professional carpenter has many tools and many types of the same tool
to get a job done, so should we use all the tools we have!

Tom Lake

 

[Bob Quintal]

The situation is that of a
SAT score conversion table. If the subject matter is "writing"
all scores must be indexed against the essay score. If the
subject matter is "reading" or "math", the score is not indexed.
So for any one given score, there can either be one data point, or
seven. It's much easier to keep mental track of the scores that
index to a particular "raw" score if you can enter them all in a
row, across. At a minimum, it means you don't have to enter the
same raw score seven times. Each practice SAT exam can have its
own score conversion table for math, reading, and writing.

Before I can proceed with the task, please explain what you mean by
indexed? do you mean scaled against the minimum and maximum?

Please explain the process of creating the conversion table?
(formulas, algotithm)

The table structure is this:

ScoreSet
===========
ScoreSetId-Autonumber, PK
SubjectName-reading, writing, math
ScoreSetDesc-will allow users to select this set later and
associate it with an exam

ScoreSetItems
============
ScoreID-Autonumber PK
ScoreSetID-FK to scoreset
WritingScore-score to index this on (will be 0 for subjects that
don't apply, 0-6 for writing)
RawScore-the actual score on the multiple choice questions
SATScore-the scaled SAT score in the given subject

is RAWScore applicable to the writing test? or only WritingScore?

How is SATScore determined? sonds like it should be a calculated
field, or looked up from another table or query.

 

[Amy Blankenship]

Before I can proceed with the task, please explain what you mean by
indexed? do you mean scaled against the minimum and maximum?

I mean it is indexed. If the raw score is 1 and the writing score is 0,
then the SAT score will be something like 220 (I don't have an exact table,
this is just an estimate). If the raw score is 1 and the writing score is
1, then the SAT score will be more like 240.

Please explain the process of creating the conversion table?

The process is of tedious data entry. Look at the source graphic, enter the
number where it goes.

 

[Amy Blankenship]

Why limit yourself to Access input only? If users are more comfortable
with Excel-type input, let them use that. Then analyze the data in
Access.

The data needs to be in the database. Why is this concept so foreign?

The whole idea behind Microsoft Office is to be able to use a suite of
programs that interoperate. You can have the front end be an Excel
worksheet while the back end is Access. I've also seen Word used to
create forms that created a data file for Excel or Access. Just as a
professional carpenter has many tools and many types of the same tool
to get a job done, so should we use all the tools we have!

Well, certainly I can write an import routine that can take a spreadsheet
and split it out into the requisite records, but this thread is about trying
to get Access to allow input of data in the format that you're actually
supposed to use in Access. If you have to go outside Access to work with
data in a format that makes sense to users, then write a routine to fix it,
Access isn't really fit for the purpose it's advertised for. And that's
more money my client has to spend without a real understanding of why, or I
just have to eat.

I'm really tired of solving this problem over and over!

 

[Amy Blankenship]

Amy,

I certainly understood what you meant the first time. The two solutions
you
were offered were vertical entry-based, which is counter to what you
stated
that you wanted. I too have been frustrated at times by this limitation,
and
in at least one case, I've intentionally denormalized some to accomodate
this
limitation. In this case, the customer wanted a spreadsheet-like view of
the
data (simple enough with a crosstab result), however, the data also had to
be
editable.

Unfortunately, I find that when I denormalize to accommodate data entry, I
regret it on the data extraction/analysis side. Either way, the client has
to spend unnecessary money or I just put in free time.

 

[Tom Lake]

The data needs to be in the database. Why is this concept so foreign?

The Excel worksheet can be linked in and used just as a native Access table.
The users should have no idea where the data is stored nor what format it's
in.
They should be able to just run their business. My users never see a query,
table, module or anything other than input Forms (which may be Word
documents,
Access forms or Excel worksheets) and Reports. It takes more work
on my part to make sure everything they need is included but it's worth it
to them.
They pay me well!

Well, certainly I can write an import routine that can take a spreadsheet
and split it out into the requisite records, but this thread is about
trying to get Access to allow input of data in the format that you're
actually supposed to use in Access. If you have to go outside Access to
work with data in a format that makes sense to users, then write a routine
to fix it, Access isn't really fit for the purpose it's advertised for.
And that's more money my client has to spend without a real understanding
of why, or I just have to eat.

That's my point. There is no reason to use Access (or any other tool)
for problems it wasn't meant to solve. Yes, Access IS limited in its input
functionality for your particular application. That's why you have to use
the proper tool for each job. If it's a combination of Excel, Access and
any
other program then that's what you do. Your client probably already has the
whole Office suite anyway. It wouldn't cost extra to combine solutions.

I'm really tired of solving this problem over and over!

and THAT is what the Office suite is meant to eliminate.

Tom Lake

 

[Amy Blankenship]

The Excel worksheet can be linked in and used just as a native Access
table.
The users should have no idea where the data is stored nor what format
it's in.
They should be able to just run their business. My users never see a
query,
table, module or anything other than input Forms (which may be Word
documents,
Access forms or Excel worksheets) and Reports. It takes more work
on my part to make sure everything they need is included but it's worth it
to them.
They pay me well!

That's great for what you need, but for what I need it _has_ to be in the
database. Please trust me to know my own requirements. I also am paid
well, though less so if I have to eat time because I can't convince my
client that he should pay for a deficiency in the tool I've chosen.

That's my point. There is no reason to use Access (or any other tool)
for problems it wasn't meant to solve. Yes, Access IS limited in its
input
functionality for your particular application. That's why you have to use
the proper tool for each job. If it's a combination of Excel, Access and
any
other program then that's what you do. Your client probably already has
the
whole Office suite anyway. It wouldn't cost extra to combine solutions.

Yes, it would.

 

[Jeff Boyce]

Amy

I rarely force my clients to learn Access. It is a power tool, not unlike a
table saw. It is not a "bookcase", like Word or Excel.

If I've done a reasonable job of creating a user interface that's
"discoverable" and well-documented (internally, not via a "user manual/code
book), the users don't even know/care that I built the application using MS
Access.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Amy Blankenship]

Amy

I rarely force my clients to learn Access. It is a power tool, not unlike
a table saw. It is not a "bookcase", like Word or Excel.

If I've done a reasonable job of creating a user interface that's
"discoverable" and well-documented (internally, not via a "user
manual/code book), the users don't even know/care that I built the
application using MS Access.

That's kind of the point of my question...

 

[Bob Quintal]

I mean it is indexed. If the raw score is 1 and the writing score
is 0, then the SAT score will be something like 220 (I don't have
an exact table, this is just an estimate). If the raw score is 1
and the writing score is 1, then the SAT score will be more like
240.

What you mean is that it's a lookup table?

The process is of tedious data entry. Look at the source graphic,
enter the number where it goes.

The source graphic must have been created by a table, somewhere, or
calculated.

 

[Amy Blankenship]

What you mean is that it's a lookup table?

In essence, but with two look up axes, which is why doing things the way
Access normally supports things is a PIA. And even to get what Access
supports, you have to use code and left or right joins.

The source graphic must have been created by a table, somewhere, or
calculated.

True, but since I don't have access to the original, hardly relevant.

 

[Bob Quintal]

In essence, but with two look up axes, which is why doing things
the way Access normally supports things is a PIA. And even to get
what Access supports, you have to use code and left or right
joins.

Aw, come on... All you need is a table with two key columns and one
value column. Not left joins. and a simple where clause in a Dmin()
function.

True, but since I don't have access to the original, hardly
relevant.

If the result set is a linear line or a simple polynomial, it can be
calculated. This might require a little code, but not necessarily.

 

[Amy Blankenship]

Aw, come on... All you need is a table with two key columns and one
value column. Not left joins. and a simple where clause in a Dmin()
function.

I'm talking about _form_ structure, not table structure. If you look at my
table structure, it is as you've described.

Please tell me how this translates to a form structure that allows the user
to enter the double-indexed value without having to repeatedly re-enter at
least one of those values that also provides enough spaces to ensure that
all six values get entered when appropriate.

Thanks;

Amy

 

[Bob Quintal]

I'm talking about _form_ structure, not table structure. If you
look at my table structure, it is as you've described.

Please tell me how this translates to a form structure that allows
the user to enter the double-indexed value without having to
repeatedly re-enter at least one of those values that also
provides enough spaces to ensure that all six values get entered
when appropriate.

Thanks;

Amy

The table structure is wrong, therefore the form structure cannot be
right.
ScoreSet
===========
ScoreSetId-Autonumber, PK
SubjectName-reading, writing, math
ScoreSetDesc-will allow users to select this set later and associate
it with an exam

ScoreSetItems
============
ScoreID-Autonumber PK
ScoreSetID-FK to scoreset
WritingScore-score to index this on (will be 0 for subjects that
don't apply, 0-6 for writing)

NewTable
============
NewTableID-Autonumber PK
ScoreID-FK to scoresetItems
RawScore-the actual score on the multiple choice questions


The following should be a calculated value, not a table field.
SATScore-the scaled SAT score in the given subject