Table Design- one or many?

[kimi]

I have just started working on a project that is partially complete. It
is an application that is using access to store test results. The test
results are being stored in two Access 2000 databases.

DB #1 = StudentDB
DB #2 = TestResulstsDB

Why are there 2 dbs? I do not know - but that is one of the tings that
we will be changing. Combining all of the data into one database.

But the current design is what I am concerned about. By no means am I an
expert - nor have I had any formal training in Programming or DB Design.
So pardon me if the question is elementary.

I feel I have a decent handle on good db design so when I realized what
was going on here - I was a bit concerned.

Ok like I mentioned above there is a Students DB and a TestResults DB
(not two tables in one databases - but two databases )

When a student takes a test the results are stored in a dynamically
built table in TestResults DB.
So if the student takes 5 tests there are 5 corresponding tables for
this student - one for each test.

Several questions - comments/concerns

#1 My instincts tell me this design will be an absolute nightmare when
trying to retrieve test results. Am I off base here?

#2 Some of the tests will have 600 records per test that will need to be
stored. So a single table with many test results could grow quickly.
Each record consists of four fields of numeric data. This is not a web
app - this is a standalone application and it will be the only app
running on this machine. With maybe one other networked machine.
How many records will be to many records in one table?

#3 There will be multiple tests with different sets of results. Would it
be beneficial to design one table per set of results or one table with
all results?
Where for each test type only some of the fields will have data -
leaving many null values in the results that are not related to the test
in question.

testresults1 testresults2
fieldA number fieldE int
fieldB number fieldF varchar(35)
fieldC number fieldG number
fieldD number
OR

testresults
fieldA number
fieldB number
fieldC number
fieldD number
fieldE int
fieldF varchar(35)
fieldG number
Thank you in advance for your help.
Kim




Please Reply to the Newsgroups..

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[PC Datasheet]

<< Why are there 2 dbs? I do not know - but that is one of the tings that we
will be changing. >>

I don't recommend you do this unless you have checked closely on why it was done
that way in the beginning. My guess is that some other bigger system generates
the student database because the students change from year to year. The old
adage is don't fix something that ain't broken.
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"kimi" <ki*@kimmyXSPAMX.com> wrote in message
news:40***********************@news.frii.net...

I have just started working on a project that is partially complete. It
is an application that is using access to store test results. The test
results are being stored in two Access 2000 databases.

DB #1 = StudentDB
DB #2 = TestResulstsDB

Why are there 2 dbs? I do not know - but that is one of the tings that
we will be changing. Combining all of the data into one database.

But the current design is what I am concerned about. By no means am I an
expert - nor have I had any formal training in Programming or DB Design.
So pardon me if the question is elementary.

I feel I have a decent handle on good db design so when I realized what
was going on here - I was a bit concerned.

Ok like I mentioned above there is a Students DB and a TestResults DB
(not two tables in one databases - but two databases )

When a student takes a test the results are stored in a dynamically
built table in TestResults DB.
So if the student takes 5 tests there are 5 corresponding tables for
this student - one for each test.

Several questions - comments/concerns

#1 My instincts tell me this design will be an absolute nightmare when
trying to retrieve test results. Am I off base here?

#2 Some of the tests will have 600 records per test that will need to be
stored. So a single table with many test results could grow quickly.
Each record consists of four fields of numeric data. This is not a web
app - this is a standalone application and it will be the only app
running on this machine. With maybe one other networked machine.
How many records will be to many records in one table?

#3 There will be multiple tests with different sets of results. Would it
be beneficial to design one table per set of results or one table with
all results?
Where for each test type only some of the fields will have data -
leaving many null values in the results that are not related to the test
in question.

testresults1 testresults2
fieldA number fieldE int
fieldB number fieldF varchar(35)
fieldC number fieldG number
fieldD number
OR

testresults
fieldA number
fieldB number
fieldC number
fieldD number
fieldE int
fieldF varchar(35)
fieldG number
Thank you in advance for your help.
Kim




Please Reply to the Newsgroups..

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[kimi]

<<<< Why are there 2 dbs? I do not know - but that is one of the tings
that we
will be changing. >>

<<I don't recommend you do this unless you have checked <<closely on why
it was done
<<that way in the beginning. My guess is that some other <<bigger system
generates
<<the student database because the students change from <<year to year.
The old
<<adage is don't fix something that ain't broken.
Hello Thank you for replying.
Perhaps my answer to this questions was a tad flippant - and it was not
meant to be. I will completely understand the entire current design -
before any design changes are made. It is my job to re-design the
database(s). The app(s) areall in development and will required some
re-design to handle the new db(s) design.

Please Reply to the Newsgroups..

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Larry Linson]

"kimi" wrote

DB #1 = StudentDB
DB #2 = TestResulstsDB

Why are there 2 dbs? I do not know -
but that is one of the tings that we will
be changing. Combining all of the data
into one database.

But the current design is what I am concerned about. By no means am I an
expert - nor have I had any formal training in Programming or DB Design.
So pardon me if the question is elementary.

I feel I have a decent handle on good db design so when I realized what
was going on here - I was a bit concerned.

Ok like I mentioned above there is a Students DB and a TestResults DB
(not two tables in one databases - but two databases )

When a student takes a test the results are stored in a dynamically
built table in TestResults DB.
So if the student takes 5 tests there are 5 corresponding tables for
this student - one for each test.

Several questions - comments/concerns

#1 My instincts tell me this design will
be an absolute nightmare when trying
to retrieve test results. Am I off base here?
You are absolutely correct. Creating multiple tables per test per student is
going to make it worse than any nightmare you've ever imagined when you need
to retrieve the information and do something with it -- a classic case of
using the tablename itself instead of data.
#2 Some of the tests will have 600 records
per test that will need to be stored. So a
single table with many test results could
grow quickly. Each record consists of four
fields of numeric data. This is not a web
app - this is a standalone application and it
will be the only app running on this machine.
With maybe one other networked machine.
How many records will be to many records
in one table?
Many people report here very acceptable performance with hundreds of
thousands or millions of records.
#3 There will be multiple tests with
different sets of results. Would it
be beneficial to design one table per
set of results or one table with all results?
Where for each test type only some of
the fields will have data - leaving many
null values in the results that are not related
to the test in question.

I don't think we have enough information to answer this question. In
general, it would probably be best to have just one table, but specific
details about the information you are keeping and how you will be using it
could indicate a different design for the specific situation.

Larry Linson
Microsoft Access MVP

[kimi]

Hi Larry Thank You for replying.

a classic case of using the tablename itself instead of data.
What do you mean using the tablename itself instead of the data?

Many people report here very acceptable performance with >>hundreds of thousands or millions of records.

Is there a defined limit where performance declines?
I don't think we have enough information to answer this >>question. In
general, it would probably be best to have just one >>table, but specificdetails about the information you are keeping and how you >>will be using itcould indicate a different design for the specific >>situation.

I don't think I have enough info yet - to ask this question. If that
makes sense. I am getting a better feel for the stat that needs to be
stored - when I have a complete understanding I will re-ask the
question.
Thank you for your time.

Please Reply to the Newsgroups..

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[kimi]

Here is a link to the proposed design for the table of test results.

http://www.kimmy.com/sampletable.html

Any reason why this data cannot be stored in the same table?

Please Reply to the Newsgroups..

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Pieter Linden]

kimi <ki*@kimmyXSPAMX.com> wrote in message news:<40***********************@news.frii.net>...

Here is a link to the proposed design for the table of test results.

http://www.kimmy.com/sampletable.html

Any reason why this data cannot be stored in the same table?

Please Reply to the Newsgroups..

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

There's really no reason why most things can't be stored in a table...
it's more usually how to *best* store them to make the tools that
SQL/Access gives you to manipulate them easily work best. So what may
look like efficient "human" design is actually database design.

Why not build your table like this: (TestNo,QuestionID,AnswerID) or
something like it? What exactly is this that you are modeling? Test
results of what? What are the rules? Is this like a survey? If so,
look up "Survey"+"Keri Hardwick" in this NG. She had some interesting
comments about it from a while back, but still very relevant. Another
question: what are you going to DO with the data? When it all comes
down to it, if you can't get the information you want out of your
database, then it's pretty much worthless. So I would start by making
sure that getting answers out of the database is a case of writing
reasonably simple queries. If you have to go through some major
effort to get an answer that should be simple, then you definitely
have a design problem.

Okay, so to rephrase Larry's question: What *exactly* are you
modeling, anyway? Are these exam scores, drug test results, what? And
*how* do you need to manipulate them? What is the business logic
behind this stuff? As yet, that's a question only you can answer.
Until you explain that, I don't think anybody can come up with a
conclusive answer to your problem, because you will not have given
sufficient information to solve it.

Hope this helps some.
Pieter

[kimi]

Hi Pieter,
Thank you for replying.

Why not build your table like this: >>(TestNo,QuestionID,AnswerID) This design would not meet my needs..

orsomething like it? What exactly is this that you are >>modeling? Test
results of what? What are the rules?
Understood - I need to give more info.
The project is a piece of s/w for a medical device that will test
patients in a rehab center. The test is for balance. This is not a
question and answer per se. It is more of a test with parameters like
Speed, length of test(30 sec, 40 sec, etc), PSI, etc. There are also
four numeric fields (Q1,Q2,Q3,Q4 which equal four quadrants of a
circle.) these fields derive their values from the computation of data
in a 2nd table. The data in the 2nd table is raw test data and consists
of four numeric fields with X Y corodinates.
Is this like a >>survey? If so,
look up "Survey"+"Keri Hardwick" in this NG. She had some
interesting
comments about it from a while back, but still very >>relevant. This is not a survey but I will look at the article.

Anotherquestion: what are you going to DO with the data? When it >>all comes
down to it, if you can't get the information you want out >>of your
database, then it's pretty much worthless. So I would >>start by makingsure that getting answers out of the database is a case >>of writing
reasonably simple queries. If you have to go through some >>major
effort to get an answer that should be simple, then you >>definitely
have a design problem.

Agreed - I have faced this problem in the past. Garbage in Garbage out.

This software will keep a history of a patients tests. Reports will need
to be run to show that this system is helping people in therapy. In
otherwords their test results are improving over time - or not
improving.

I cannot completely answer what determines improvement. I think it will
be the the computed values of Q1, Q2, Q3, Q4 . I understand that this is
a key component of hwo to design the db.

I am still in the process of learning what they want and need. forgive
me if I have missed something.
If you can offer any opion on design it would be most appreciated.
Thank you in advance.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[kimi]

On a second note - my original question was regarding the different data
stored in the table.

There are approximately a dozen tests - with half of the tests requiring
all of the fields for storage - where the 2nd half of tests only need
one field for a compiled score.
is there a problem storing both of these test results in the same table.
I will have 90% of the fields null for half the test types.

In the future there will be more tests added. I am going to assume that
these tests may use some of the same fields and will require other
fields to be added.

Am I better off dividing the test types based on similar test results?
Please Reply to the Newsgroups..

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Roger]

this is what I see so far... what is missing ?

testInfo
testId autonumber (pk)
testName text(30)
speed
duration
pressure

patientInfo
patientId autonumber (pk)
patientName text

[kimi]

Hi Roger,
Thank you for replying.

testInfo
testId autonumber (pk)
testName text(30)
speed
duration
pressure
In my current design I have the testInfo combined with the test results
- I just realized why that should not be...
Test info is the test environment - and the results are a product of
that environment. Does that make sense? So they are not in the same set
of data.
Thanks - I would've missed this.

patientInfo
patientId autonumber (pk)
patientName text
.
. other patient data
. Yes - this is what I have
testResults
resultId autonumber (pk)
patientId (fk)
testId (fk)
resultDate
q1x
q1y
q2x
q2y
q3x
q3y
q4x
q4y

Similar to what I have - but I do not have 8 quadrants fields - I have 4
Q1, Q2, Q3, Q4
But I am considering - dropping these fields. And just storing the raw
data.

ie.. testinfo
testId 20
testName whirl
speed 3600 RPM
duration 30 secs
pressure 100 psi patientInfo
patientId 100
patientName Roger
testResults
resultId 6666
patientId 100
testId 20
resultDate #01/04/04#
q1x 1
q1y 20
q2x 2
q2y 30
q3x 3
q3y 46
q4x 4.5
q4y 34.21
since Q1,Q2,Q3,Q4 are derived from (qx, qy), they don't >>need to be

stored
Agreed - but I was thinking the other way around - if I have the Q's do
I need to save the raw data? Yes - I better - what if a mistake is made
computing the Q's - I would be up the proverbial creek.

Now add to this mix - a second type of test - the test results would be
a single score, this value would be derived from raw data stored in
another table. So the score itself would not be written to the db. I
would assume there would be a record in testInfo - but speed duration
and presure would not be needed.
And I am sure in the future there will be other tests added that will
requires a different environment and different result sets.

Thank you again for the help.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Roger]

Kimi

your description states
....The project is a piece of s/w for a medical device that will
test
patients in a rehab center. The test is for balance. This is not a
question and answer per se. It is more of a test with parameters
like
Speed, length of test(30 sec, 40 sec, etc), PSI, etc. There are
also
four numeric fields (Q1,Q2,Q3,Q4 which equal four quadrants of a
circle.) these fields derive their values from the computation of
data
in a 2nd table. The data in the 2nd table is raw test data and
consists
of four numeric fields with X Y corodinates...

thus my reasoning to store Qx(1-4) and Qy(1-4), ie. the raw data
this allows you to compute anything, now and in the future

how can a mistake by made in computing Q1-Q4 ? isn't it a standard
algorithm that can be coded as a 'function' and used where required ?

'second type of test ... score..'
testInfo would have the parameters for this other type of test
testResults would have a field called 'score' or 'score data' from
which
score is computed

if you're going to have many tests of one type, and many other tests
of another
type... it might be an idea to create a type

testType
testTypeId autonumber (pk)
testType text

and add 'testTypeId' as a foreign key to the testInfo table

In thinking as I type, maybe the testTypeId structure should contain a
list of
valid testInfo fields (id=1 -> speed, duration, pressure)
id=2 -> score test data)

valid testResult fields (id=1 -> Qx(1-4) and Qy(1-4)
id=2 -> score test result)

doing this, testInfo and testResults wouldn't contain a superlist
of
all fields for all test types... but would point to a table
structure that
allows you to store just test type specific information

I need to think this through...

kimi <ki*@kimmyXSPAMX.com> wrote in message news:<40***********************@news.frii.net>...

Hi Roger,
Thank you for replying.

testInfo
testId autonumber (pk)
testName text(30)
speed
duration
pressure
In my current design I have the testInfo combined with the test results
- I just realized why that should not be...
Test info is the test environment - and the results are a product of
that environment. Does that make sense? So they are not in the same set
of data.
Thanks - I would've missed this.

patientInfo
patientId autonumber (pk)
patientName text
.
. other patient data
. Yes - this is what I have
testResults
resultId autonumber (pk)
patientId (fk)
testId (fk)
resultDate
q1x
q1y
q2x
q2y
q3x
q3y
q4x
q4y

Similar to what I have - but I do not have 8 quadrants fields - I have 4
Q1, Q2, Q3, Q4
But I am considering - dropping these fields. And just storing the raw
data.

ie..testinfo
testId 20
testName whirl
speed 3600 RPM
duration 30 secs
pressure 100 psipatientInfo
patientId 100
patientName Roger
testResults
resultId 6666
patientId 100
testId 20
resultDate #01/04/04#
q1x 1
q1y 20
q2x 2
q2y 30
q3x 3
q3y 46
q4x 4.5
q4y 34.21
since Q1,Q2,Q3,Q4 are derived from (qx, qy), they don't >>need to be

stored
Agreed - but I was thinking the other way around - if I have the Q's do
I need to save the raw data? Yes - I better - what if a mistake is made
computing the Q's - I would be up the proverbial creek.

Now add to this mix - a second type of test - the test results would be
a single score, this value would be derived from raw data stored in
another table. So the score itself would not be written to the db. I
would assume there would be a record in testInfo - but speed duration
and presure would not be needed.
And I am sure in the future there will be other tests added that will
requires a different environment and different result sets.

Thank you again for the help.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Roger]

Kimi,

still thinking... got this so far, which allows for unlimited stimulus
and measurement points...

testType
testTypeId (pk)
description

testStimulus
testStimulusId (pk)
stimulusName ie. speed, duration, pressure
testTypeId (fk)
stimulusDataType
promptSeq

testMeasurement
testMeasurementId (pk)
measurementName ie. q1x, q1y, Q1, Q2, score
testTypeId (fk)
stimulusDataType
promptSeq

testInfo
testInfoId (pk)
description
testStimulusId (fk)
numericValue ie. 3600 RPM, 10 minutes, 100 PSI
stringValue
testResult
testResultId (pk)
testInfoId (fk)
testMeasurementId (fk)
contactId (fk) ie. patient
resultDate
numericValue
stringValue

the 'promptSeq' is used in the UI (maybe a 2-column list box)...

thoughts ?

kimi <ki*@kimmyXSPAMX.com> wrote in message news:<40***********************@news.frii.net>...

Hi Roger,
Thank you for replying.

testInfo
testId autonumber (pk)
testName text(30)
speed
duration
pressure
In my current design I have the testInfo combined with the test results
- I just realized why that should not be...
Test info is the test environment - and the results are a product of
that environment. Does that make sense? So they are not in the same set
of data.
Thanks - I would've missed this.

patientInfo
patientId autonumber (pk)
patientName text
.
. other patient data
. Yes - this is what I have
testResults
resultId autonumber (pk)
patientId (fk)
testId (fk)
resultDate
q1x
q1y
q2x
q2y
q3x
q3y
q4x
q4y

Similar to what I have - but I do not have 8 quadrants fields - I have 4
Q1, Q2, Q3, Q4
But I am considering - dropping these fields. And just storing the raw
data.

ie..testinfo
testId 20
testName whirl
speed 3600 RPM
duration 30 secs
pressure 100 psipatientInfo
patientId 100
patientName Roger
testResults
resultId 6666
patientId 100
testId 20
resultDate #01/04/04#
q1x 1
q1y 20
q2x 2
q2y 30
q3x 3
q3y 46
q4x 4.5
q4y 34.21
since Q1,Q2,Q3,Q4 are derived from (qx, qy), they don't >>need to be

stored
Agreed - but I was thinking the other way around - if I have the Q's do
I need to save the raw data? Yes - I better - what if a mistake is made
computing the Q's - I would be up the proverbial creek.

Now add to this mix - a second type of test - the test results would be
a single score, this value would be derived from raw data stored in
another table. So the score itself would not be written to the db. I
would assume there would be a record in testInfo - but speed duration
and presure would not be needed.
And I am sure in the future there will be other tests added that will
requires a different environment and different result sets.

Thank you again for the help.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Roger]

Kimi
I've created an mdb with the following structure...
allows you to define the objects (stimulus / response) that make up a
test.. and to collect the results

let me know if you interested in a copy

Table: tblContactInfo
Columns
Name Type Size
contactId Number (Long) 4
contactName Text 50
address Text 50
city Text 30
provStateId Text 4
postalZip Text 10
phone Text 15
fax Text 15
email Text 50

Table: tblCountry
Columns
Name Type Size
countryId Text 3
name Text 30

Table: tblDataType (ie. Number, string, date, etc)
Columns
Name Type Size
dataTypeId Number (Single) 4
description Text 30
precision Number (Single) 4

Table: tblObject
Columns
Name Type Size
objectId Number (Long) 4
objectName Text 30
objectTypeId Number (Single) 4
dataTypeId Number (Single) 4
uomId Number (Long) 4

Table: tblObjectType (ie. Stimulus, response)
Columns
Name Type Size
objectTypeId Number (Single) 4
objectTypeName Text 12

Table: tblProvinceState
Columns
Name Type Size
provStateId Text 4
name Text 30
countryId Text 4

Table: tblTestDefinition
Columns
Name Type Size
definitionId Number (Long) 4
description Text 30
testTypeId Number (Long) 4

Table: tblTestDefinitionObject
Columns
Name Type Size
definitionId Number (Long) 4
objectId Number (Long) 4
numericValue Number (Double) 8
stringValue Text 30
promptSeq Number (Integer) 2

Table: tblTestResult
Columns
Name Type Size
testResultId Number (Long) 4
testDefinitionId Number (Long) 4
contactId Number (Long) 4
resultDate Date/Time 8

Table: tblTestResultObject
Columns
Name Type Size
testResultId Number (Long) 4
objectId Number (Long) 4
numericValue Number (Double) 8
stringValue Text 30
Table: tblTestType (ie. Engine, fitness, math)
Columns
Name Type Size
testTypeId Number (Long) 4
description Text 30

Table: tblUom (ie. PSI, INCH,RPM)
Columns
Name Type Size
uomId Number (Long) 4
unitOfMeasure Text 20

le*********@natpro.com (Roger) wrote in message news:<8c**************************@posting.google. com>...

Kimi,

still thinking... got this so far, which allows for unlimited stimulus
and measurement points...

testType
testTypeId (pk)
description

testStimulus
testStimulusId (pk)
stimulusName ie. speed, duration, pressure
testTypeId (fk)
stimulusDataType
promptSeq

testMeasurement
testMeasurementId (pk)
measurementName ie. q1x, q1y, Q1, Q2, score
testTypeId (fk)
stimulusDataType
promptSeq

testInfo
testInfoId (pk)
description
testStimulusId (fk)
numericValue ie. 3600 RPM, 10 minutes, 100 PSI
stringValue
testResult
testResultId (pk)
testInfoId (fk)
testMeasurementId (fk)
contactId (fk) ie. patient
resultDate
numericValue
stringValue

the 'promptSeq' is used in the UI (maybe a 2-column list box)...

thoughts ?

kimi <ki*@kimmyXSPAMX.com> wrote in message news:<40***********************@news.frii.net>...

Hi Roger,
Thank you for replying.

>testInfo
> testId autonumber (pk)
> testName text(30)
> speed
> duration
> pressure

In my current design I have the testInfo combined with the test results
- I just realized why that should not be...
Test info is the test environment - and the results are a product of
that environment. Does that make sense? So they are not in the same set
of data.
Thanks - I would've missed this.

>patientInfo
> patientId autonumber (pk)
> patientName text
> .
> . other patient data
> .

Yes - this is what I have

>testResults
> resultId autonumber (pk)
> patientId (fk)
> testId (fk)
> resultDate
> q1x
> q1y
> q2x
> q2y
> q3x
> q3y
> q4x
> q4y
>

Similar to what I have - but I do not have 8 quadrants fields - I have 4
Q1, Q2, Q3, Q4
But I am considering - dropping these fields. And just storing the raw
data.

>ie..testinfo
> testId 20
> testName whirl
> speed 3600 RPM
> duration 30 secs
> pressure 100 psipatientInfo
> patientId 100
> patientName Roger

>testResults
> resultId 6666
> patientId 100
> testId 20
> resultDate #01/04/04#
> q1x 1
> q1y 20
> q2x 2
> q2y 30
> q3x 3
> q3y 46
> q4x 4.5
> q4y 34.21

>since Q1,Q2,Q3,Q4 are derived from (qx, qy), they don't >>need to be

stored
Agreed - but I was thinking the other way around - if I have the Q's do
I need to save the raw data? Yes - I better - what if a mistake is made
computing the Q's - I would be up the proverbial creek.

Now add to this mix - a second type of test - the test results would be
a single score, this value would be derived from raw data stored in
another table. So the score itself would not be written to the db. I
would assume there would be a record in testInfo - but speed duration
and presure would not be needed.
And I am sure in the future there will be other tests added that will
requires a different environment and different result sets.

Thank you again for the help.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[kim]

Roger,
I have been trying to answer your post for over a week now.
this is the fourth reply - the first via google as opposed to DevDex.
I hope this works. For whatever reason - I cannot post a message via
Devdex anymore.
I have even sent messages to DevDex and not received a reply regarding
the problem. Anyone else have this problem?
I have since turned in my design which was close to your first
suggestions.

But I have a question about the tables below.

the two fields numericValue and stringValue will store numeric and
string data - these values may be numeric - but may not be the same
range for each result or definition.

is there any way to maintain a rule against these values if there
could be different rules for different results?

example: one range may be from 1 to 10 , and another range could be
from 5 - 15

I see no way of maintaining rules.

Table: tblTestDefinitionObject
Columns
Name Type Size
definitionId Number (Long) 4
objectId Number (Long) 4
numericValue Number (Double) 8
stringValue Text 30
promptSeq Number (Integer) 2

Table: tblTestResultObject
Columns
Name Type Size
testResultId Number (Long) 4
objectId Number (Long) 4
numericValue Number (Double) 8
stringValue Text 30

[Roger]

umm... Access has an eval() function that returns true or false

so you could add a column to the tblTestDefinitionObject table called

validationRule text(200)

and it would contain strings like

strComp(@,"pump",1) = 0
(@ > 1 and @ < 10)

then in the 'change' event of the form providing the answer you would
called evaluateRule (air code) and check for True

Public Function evaluateRule
Dim intFrom As Integer

On Error GoTo fErr
evaluateRule = False
Do
intFrom = InStr(1, strRule, "@", vbTextCompare)
If (intFrom > 0) Then
strRule = Left$(strRule, intFrom - 1) & _
<<the answer provided>> & _
Right$(strRule, Len(strRule) - intFrom + 1)
Else
Exit Do
End If
Loop

evaluateRule = Eval(strRule)
fExit:
Exit Function

fErr:
MsgBox "evaluateRule error " & Err.Description
evaluateRule = False
Resume fExit
end function
if you want to reference other variables in the test results you could
use
'[', ']' to mark the field names and change evaluateRule()
accordingly, so your rule might be

([Q2] = 8 AND ([Q1] > 1 AND [Q1] < 10)) OR ([Q2] = 6 AND ([Q1] > 5
and [Q1] < 15))
let me know if you need more clarification

ki**@dealerstore.net (kim) wrote in message news:<18**************************@posting.google. com>...

Roger,
I have been trying to answer your post for over a week now.
this is the fourth reply - the first via google as opposed to DevDex.
I hope this works. For whatever reason - I cannot post a message via
Devdex anymore.
I have even sent messages to DevDex and not received a reply regarding
the problem. Anyone else have this problem?
I have since turned in my design which was close to your first
suggestions.

But I have a question about the tables below.

the two fields numericValue and stringValue will store numeric and
string data - these values may be numeric - but may not be the same
range for each result or definition.

is there any way to maintain a rule against these values if there
could be different rules for different results?

example: one range may be from 1 to 10 , and another range could be
from 5 - 15

I see no way of maintaining rules.

Table: tblTestDefinitionObject
Columns
Name Type Size
definitionId Number (Long) 4
objectId Number (Long) 4
numericValue Number (Double) 8
stringValue Text 30
promptSeq Number (Integer) 2

Table: tblTestResultObject
Columns
Name Type Size
testResultId Number (Long) 4
objectId Number (Long) 4
numericValue Number (Double) 8
stringValue Text 30