dependent fields

[kam]

i'm relatively new to access and appreciate any help you can give...

what i THOUGHT i wanted to do was set up dependent fields, but then read
that "In the ideal database design, the fields are not dependent on each
other, so if you are comparing fields, you might consider whether there is
another way to design the table".

i'm trying to compare a person's test score to the passing score for that
test, which varies from test to test, but i want to get a
percentage-above-passing number that can be used to compare people who take
different tests. (does that make sense?)

can anyone advise me on how to set up a field so that it generates a value
based on what data is entered in another field in that record (i.e. fills in
the passing score based on which test i choose for that test-taker), OR a
better way to design it so i don't have to do that?

i also need to do the same kind of thing in a different situation: i have an
evaluation rubric that scores on a scale of 1-5, with a N/A option. i want to
use the database to get the persons score out of the total possible points.
so for each criteria on which the person scores a 1, 2, 3, 4, or 5, the total
possible points are 5. but if they score a N/A the total possible points are
0. the total possible points will then be added up...

thanks very much for any help you can give me!!

 

[Jamie Collins]

what i THOUGHT i wanted to do was set up dependent fields, but then read
that "In the ideal database design, the fields are not dependent on each
other, so if you are comparing fields, you might consider whether there is
another way to design the table".

i'm trying to compare a person's test score to the passing score for that
test, which varies from test to test, but i want to get a
percentage-above-passing number that can be used to compare people who take
different tests. (does that make sense?)

can anyone advise me on how to set up a field so that it generates a value
based on what data is entered in another field in that record (i.e. fills in
the passing score based on which test i choose for that test-taker), OR a
better way to design it so i don't have to do that?

Take a look at this previous thread, which touches on the arguments
for and against repeat values in this way:

http://groups.google.com/group/microsoft.public.access/msg/1d4430dc8b50ba8f

The idea behind 'Approach 1' is that the value from the referenced
table is repeated in the referencing table in order to use DRI
("Relationship with Referential Integrity enforced") and a record
level Validation Rule, concepts which most of the regulars seem to be
happy with, though the slight denormalization (bloated key) may not
sit too comfortably. The alternative 'Approach 2' uses a table-level
CHECK constraint, which seemingly most of the regulars have barely
heard of, let alone use on a regular basis.

Jamie.

--

 

[Jeff Boyce]

You'll find a variety of (volunteer) responders in these newsgroups. Some
folks will encourage you to design and use what they consider to be an
optimal approach.

Some folks will offer solutions that "satisfice" (good enough to get the job
done, but not necessarily elegant or optimal).

And some folks offer ideas out of left field.

Part of the challenge you face in use the 'groups is figuring out which (of
the first two, hopefully) approach works better for you, while avoiding the
third.

Another piece of advice you'll see here is to not store calculated values
(in tables). Instead, use queries to do the calculations.

You might want to look at a sample db Duane H. put together (At Your Survey)
to help with tests and surveys -- it sounds like it might apply in your
situation:

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jamie Collins]

You'll find a variety of (volunteer) responders in these newsgroups. Some
folks will encourage you to design and use what they consider to be an
optimal approach.

Some folks will offer solutions that "satisfice" (good enough to get the job
done, but not necessarily elegant or optimal).

And some folks offer ideas out of left field.

Part of the challenge you face in use the 'groups is figuring out which (of
the first two, hopefully) approach works better for you, while avoiding the
third.

Another piece of advice you'll see here is to not store calculated values
(in tables). Instead, use queries to do the calculations.

I love the opening lines of this article:

Calculated Columns
by Joe Celko
http://www.dbazine.com/ofinterest/oi-articles/celko4/

"You are not supposed to put a calculated column in a table in a pure
SQL database. And as the guardian of pure SQL, I should oppose this
practice. Too bad the real world is not as nice as the theoretical
world."

Normalization is A Good Thing but sometimes you have to at least
consider breaking the rules to get things done; the classic phrase is
"denormalize for performance" (noting that some respond that if your
normalized design runs like glue on your relational technology then
it's time to get some new relational technology).

One of the issues I see around here (but many miss) is where a
normalized design makes constraints harder to define, typically
requiring a table level CHECK constraint. Even some common design
patterns (e.g. a history table) require table level constraints to
enforce a basic primary key (lowercase); virtually everyone says that
every table should have a primary key -- or do they merely mean it
PRIMARY KEY (uppercase)? -- yet when I point out to someone that they
proposed design requires table level CHECK constraints I get seemingly
daft replies such as, "If this were SQL Server I'd do it with triggers
but it's not possible with Access", and "I'd do that with VBA in the
front end" and "It would be nice if Jet supported CHECK
constraints..." Now consider that some (most?) people who have
considered it think *any* solution that requires a table-level CHECK
constraint is 'out of left field'...

Let's me use the OP's example. The following structure I consider to
be normalised (5NF, I think):

CREATE TABLE Students (
student_number INTEGER NOT NULL UNIQUE
)
;
CREATE TABLE Tests (
test_ID INTEGER NOT NULL UNIQUE,
max_score INTEGER NOT NULL,
CHECK (max_score > 0)
)
;
CREATE TABLE TestResults (
student_number INTEGER NOT NULL
REFERENCES Students (student_number),
test_ID INTEGER NOT NULL
REFERENCES Tests (test_ID),
UNIQUE (student_number, test_ID),
score INTEGER NOT NULL,
CHECK (score > 0),
CONSTRAINT test_result_score_greater_than_maximum
CHECK (NOT EXISTS (
SELECT *
FROM Tests AS T1, TestResults AS R1
WHERE T1.test_ID = R1.test_ID
AND R1.score > T1.max_score
))
)
;

The issue is the table-level CHECK constraint (the last named one; the
others may be replaced with Validation Rules). I'm comfortable with
it, despite the issues, but I know many are not. Therefore, I'll offer
an alternative with I consider to be denormalized but still effective:

CREATE TABLE Students (
student_number INTEGER NOT NULL UNIQUE
)
;
CREATE TABLE Tests (
test_ID INTEGER NOT NULL UNIQUE,
max_score INTEGER NOT NULL,
CHECK (max_score > 0),
UNIQUE (test_ID, max_score)
)
;
CREATE TABLE TestResults (
student_number INTEGER NOT NULL
REFERENCES Students (student_number),
test_ID INTEGER NOT NULL,
UNIQUE (student_number, test_ID),
max_score INTEGER NOT NULL,
FOREIGN KEY (test_ID, max_score)
REFERENCES Tests (test_ID, max_score),
score INTEGER NOT NULL,
CHECK (score BETWEEN 0 AND max_score)
)
;

There are no issues with the CHECK constraints here because all may be
replaced with Validation Rules. This is made possible by repeating
max_score from the Tests table in the TestResults. I think this is a
small price to pay for being able to utilise constraints (Validation
Rules and 'Relationships with Referential Integrity enforced' which
are familiar to and popular with most experienced Access users. That
said, hand on heart I'd personally stick with the fully normalised
design and would avoid actively promoting any denormalized design but
I do at least recognise that one cannot always be a purist about such
matters. Data integrity, on the other hand, I will never compromise
on.

Jamie.

--