design dilemma...

[PieterLinden]

I am working on a database that, among other things, will be used to
record Chemistry Lab Values (it's for Cancer Treatment).

The structure of the table is like this:

CREATE TABLE PatientLabs(
Cycle INTEGER,
PatientID LONG INTEGER,
Lab TEXT(50),
Normal BOOLEAN,
ClinicallySignificant BOOLEAN,
Comment MEMO,
PRIMARY KEY(Cycle, PatientID, Lab));

The lab tests are fixed - there are about 20 of them. The form based
on this table would be a subform inside of the Patient form (if it
matters). The catch is that if I do this, I can't make any columns
required except the PK... or am I missing something? Maybe I should
figure out how At Your Survey does validation? Is that it? (sorry,
thinking out loud a little...)

is there a way that's staring me in the face that I can't see? Any
pointers/thoughts greatly appreciated!

Thanks!

Pieter

 

[Jamie Collins]

I am working on a database that, among other things, will be used to
record Chemistry Lab Values (it's for Cancer Treatment).

The structure of the table is like this:

CREATE TABLE PatientLabs(
Cycle INTEGER,
PatientID LONG INTEGER,
Lab TEXT(50),
Normal BOOLEAN,
ClinicallySignificant BOOLEAN,
Comment MEMO,
PRIMARY KEY(Cycle, PatientID, Lab));

The lab tests are fixed - there are about 20 of them. The form based
on this table would be a subform inside of the Patient form (if it
matters). The catch is that if I do this, I can't make any columns
required except the PK... or am I missing something? Maybe I should
figure out how At Your Survey does validation? Is that it? (sorry,
thinking out loud a little...)

is there a way that's staring me in the face that I can't see? Any
pointers/thoughts greatly appreciated!

Thanks!

Pieter

Use NOT NULL in the SQL DDL.

Note that LONG INTEGER is not valid, use simply INTEGER to get the
equivalent of a VBA Long Integer; for the equivalent of a VBA Integer
use SMALLINT. Also, BOOLEAN is not valid in Jet SQL so instead try
LOGICAL e.g.

CREATE TABLE PatientLabs(
Cycle SMALLINT NOT NULL,
PatientID INTEGER NOT NULL,
Lab TEXT(50) NOT NULL,
Normal LOGICAL NOT NULL,
ClinicallySignificant LOGICAL NOT NULL,
Comment MEMO NOT NULL,
PRIMARY KEY(Cycle, PatientID, Lab));

For Jet data types in SQL DDL and their synonyms, see:

Intermediate Microsoft Jet SQL for Access 2000
http://msdn2.microsoft.com/en-us/library/aa140015(office.10).aspx

Jamie.

--

 

[Larry Daugherty]

FWIW I'd try doing the schema using Access's User Interface. By doing
it that way you don't have to keep creating your tables from scratch
and thereby save yourself continually re-entering or importing any
data that already exists.. Allow yourself the possibility that you
won't get it perfectly before you put your application in use.

HTH

 

[PieterLinden]

Use NOT NULL in the SQL DDL.

Note that LONG INTEGER is not valid, use simply INTEGER to get the
equivalent of a VBA Long Integer; for the equivalent of a VBA Integer
use SMALLINT. Also, BOOLEAN is not valid in Jet SQL so instead try
LOGICAL e.g.

CREATE TABLE PatientLabs(
Cycle           SMALLINT NOT NULL,
PatientID       INTEGER NOT NULL,
Lab             TEXT(50) NOT NULL,
Normal          LOGICAL NOT NULL,
ClinicallySignificant   LOGICAL NOT NULL,
Comment         MEMO NOT NULL,
PRIMARY KEY(Cycle, PatientID, Lab));

For Jet data types in SQL DDL and their synonyms, see:

Intermediate Microsoft Jet SQL for Access 2000http://msdn2.microsoft.com/en-us/library/aa140015(office.10).aspx

Jamie.

--- Hide quoted text -

- Show quoted text -

I guess I should rephrase the question. The syntax is a crossbreed.
My apologies. Is the basic idea of having a structure like this
sound? I was thinking I could then populate it by doing a cartesian
product of Patient X LabTestList and then turning it into an append
query. True, I lose the ability to do table-level validation, but I
guess I could do it in the BeforeInsert event of the subform.... Is
this a sound plan?

Hope that's clearer. Sorry for the confusion!

Pieter

 

[Pat Hartman]

I don't know what cycle is so I can't say anything about it. I'm not sure
what Lab is either. It seems that it should be the ID of the test being
performed (FK to the table that defines all the possible Labs) rather than a
text string. Don't you also need the results of the test? Normal and
ClinicallySignificant don't give much information. I would also combine
Normal and ClinicallySignificant into a single field with different states.
Use radio buttons or a combo to let the user choose the value - whichever
works best on the form. And finally, I would use an autonumber as the PK
and if you have a business rule regarding uniqueness for the three items
currently identified as the compound PK, I would make them a unique index.

Use NOT NULL in the SQL DDL.

Note that LONG INTEGER is not valid, use simply INTEGER to get the
equivalent of a VBA Long Integer; for the equivalent of a VBA Integer
use SMALLINT. Also, BOOLEAN is not valid in Jet SQL so instead try
LOGICAL e.g.

CREATE TABLE PatientLabs(
Cycle SMALLINT NOT NULL,
PatientID INTEGER NOT NULL,
Lab TEXT(50) NOT NULL,
Normal LOGICAL NOT NULL,
ClinicallySignificant LOGICAL NOT NULL,
Comment MEMO NOT NULL,
PRIMARY KEY(Cycle, PatientID, Lab));

For Jet data types in SQL DDL and their synonyms, see:

Intermediate Microsoft Jet SQL for Access
2000http://msdn2.microsoft.com/en-us/library/aa140015(office.10).aspx

Jamie.

--- Hide quoted text -

- Show quoted text -

I guess I should rephrase the question. The syntax is a crossbreed.
My apologies. Is the basic idea of having a structure like this
sound? I was thinking I could then populate it by doing a cartesian
product of Patient X LabTestList and then turning it into an append
query. True, I lose the ability to do table-level validation, but I
guess I could do it in the BeforeInsert event of the subform.... Is
this a sound plan?

Hope that's clearer. Sorry for the confusion!

Pieter

 

[Jamie Collins]

FWIW I'd try doing the schema using Access's User Interface. By doing
it that way you don't have to keep creating your tables from scratch
and thereby save yourself continually re-entering or importing any
data that already exists.. Allow yourself the possibility that you
won't get it perfectly before you put your application in use.

Funny, I create schema using SQL DDL for exactly those reasons e.g. if
during design time I need to tweak the design then I simply drop the
tables (also scripted using SQL DDL, natch <g>), modify the script and
re-run. When you do so using the Access interface you don't get an
'audit trail', unlike when I check your SQL DDL code into source
control. Even if one doesn't use SQL DDL in reality it is still a fine
way of posting a schema in the newsgroups and I think it something to
be actively encouraged rather than discouraged.

Jamie.

--

 

[Jamie Collins]

And finally, I would use an autonumber as the PK
and if you have a business rule regarding uniqueness for the three items
currently identified as the compound PK, I would make them a unique index.

I would respectfully request that when you introduce something (OT)
into the schema (discussion) you give good reasons for doing so

Jamie.

--