form setup

K

Karen

I am trying to create a form for my data, but each time I set it up something
different doesn't quite work, so I suspect there is something wrong with my
approach. I would be very grateful if someone could suggest an appropriate
set up for the following data.
I have three tables,

1. Patient Demographic Data
Hospital Number (primary key)
Surname
Name
etc

2. Event
Case Number (primary key)
Hospital Number
Date
Procedure

3. Results
Laboratory Number (primary key)
Case Number
testtype1
testtype2
etc

I have 1-many relationships between Hospital number (Patient Demographic
Data ->Event) and Case Number (Event->results).
I have a query linking all of the above data (Full Database Query).
I would like the form to be based on Patient Demographic Data and Event,
with Results being the subform.

Up till now the problems have included being unable to enter any data in the
subform (ie "can't have a null value on primary key" whilst actually tring to
enter the data in question), errors in connecting the tables (jet database
engine errors) or being unable to introduce a new patient to the database.

Thanks in advance
Karen
 
J

John W. Vinson

I am trying to create a form for my data, but each time I set it up something
different doesn't quite work, so I suspect there is something wrong with my
approach. I would be very grateful if someone could suggest an appropriate
set up for the following data.
I have three tables,

1. Patient Demographic Data
Hospital Number (primary key)
Surname
Name
etc

2. Event
Case Number (primary key)
Hospital Number
Date
Procedure

3. Results
Laboratory Number (primary key)
Case Number
testtype1
testtype2
etc

I have 1-many relationships between Hospital number (Patient Demographic
Data ->Event) and Case Number (Event->results).
I have a query linking all of the above data (Full Database Query).
I would like the form to be based on Patient Demographic Data and Event,
with Results being the subform.

Up till now the problems have included being unable to enter any data in the
subform (ie "can't have a null value on primary key" whilst actually tring to
enter the data in question), errors in connecting the tables (jet database
engine errors) or being unable to introduce a new patient to the database.

If your Form is based on this "grand master query" joining everything...
that's your problem. Such queries are appropriate for reports but not for data
editing/entry forms.

I'd base the main form on the Patient Demographic Data table; a (single form
view) Subform on the Event table, using the [Hospital Number] as the
master/child link field; and a sub-subform on the Results table.

I'm queasy about your Results table structure though. What if you need to add
a new test type? You'll need to add a new field to your table, redesign all
your queries, redesign all your forms, redesign all your reports.... OUCH!!
Surely this should be yet another one to many relationship?
 
P

Piet Linden

I agree with John. The only reason I'm answering is that I spent six
months normalizing unnormalized data structures. That said, here are
a few things that might bite you...
1. storing test names as column names... e.g. Instead of having
(PatientID, TestDate, TestName, Reading)

they had (PatientID, TestDate, Test1Name, Test2Name... to Test[n]Name)
so summarizing information like that was a *nightmare*. Then what
does a null value under a test mean? Test wasn't done? No result
recorded? It gets *very* ugly *very* fast.

My structure was something like:

Patient----(1,M)---Serious Adverse Event----(1,M)---Toxicity---(M,1)---
Toxicity List (from Theradex)---(M,1)---Tox Group

If your structure is sound, then you can get pretty much *any* answer
and the design remains constant. (No need to add columns for new
information.) The alternative is a disaster. Been there, done it.
Fixing it took a LOT of work. So do yourself a favor and do it right
the first time - even if it means sitting down with a pencil and paper
and diagramming everything on paper. (easier to throw away and modify
without pain!). Then check to make sure you can answer any question
people can think of with your model. IF you do that, your database
structure is fine.
 
K

Karen

Thank you both for the helpful advice. I agree with you that there could be
potential problems with the database and its current design if I ever want to
add more tests, yet I can't quite visualise how to apply your suggestions.
Currently I have 5 test types, 2 of which are yes/no answers. To two of the
other test types I want to apply validation rules to limit typing errors
during data entry...If I change the setup to 'testname' and
'reading/measurement' do I then loose the ability to be able to do this?
Also, I don't want to have to type in each test name at data entry, I'd
prefer to be able to type 'result A' directly into 'test A'.
I have set up a series of different queries that seem to successfully pull
out the data I'm looking for in various combinations.

I've set up the form as John suggested and it has solved any problems as far
as entering data goes but I'd like to sort the all data by Case Number and I
can't do that if it is the first subform, are there any alternatives?

thanks again for the help, I really am trying to make this db as sensible as
possible and avoid the pitfalls!!

K
 
J

John W. Vinson

Thank you both for the helpful advice. I agree with you that there could be
potential problems with the database and its current design if I ever want to
add more tests, yet I can't quite visualise how to apply your suggestions.
Currently I have 5 test types, 2 of which are yes/no answers.

Then you need a Tests table with (currently) five rows, one for each test
type; and you also need a table to store the answers. It would be related
one-to-many to your Visits table and also one to many to your Tests table. If
a patient underwent three tests there would be three records in this table.
You'ld use a Subform (a sub-sub-subform perhaps) to enter the results.
To two of the
other test types I want to apply validation rules to limit typing errors
during data entry...If I change the setup to 'testname' and
'reading/measurement' do I then loose the ability to be able to do this?

Of course not. You would use a Combo Box to allow the user to *SELECT* the
test, not require them to type it at all.
Also, I don't want to have to type in each test name at data entry, I'd
prefer to be able to type 'result A' directly into 'test A'.
Exactly.

I have set up a series of different queries that seem to successfully pull
out the data I'm looking for in various combinations.

Again: if you're trying to set up One Grand Master Query to do everything,
you're on the wrong track! Access Forms are *very* powerful and provide all
the tools you need to get this.
I've set up the form as John suggested and it has solved any problems as far
as entering data goes but I'd like to sort the all data by Case Number and I
can't do that if it is the first subform, are there any alternatives?

Please explain. "Sort" means "to put records in a particular sequential
order". If you're working on entering data for a specific patient, what
difference does the order of the *other* records in the table (that you don't
see onscreen) make?

If you want to base your mainform on a query of the Cases table, sorted by
case number, that works too. You have *a lot of choices* - mine was a
suggestion; if it doesn't fit your needs, make another choice!


thanks again for the help, I really am trying to make this db as sensible as
possible and avoid the pitfalls!!

One possible concern: you don't actually say, but it sounds like you may be
working in table or query datasheets. DON'T. Datasheets are very limited;
Forms are much more appropriate.
 
K

Karen

Then you need a Tests table with (currently) five rows, one for each test
type; and you also need a table to store the answers. It would be related
one-to-many to your Visits table and also one to many to your Tests table. If
a patient underwent three tests there would be three records in this table.
You'ld use a Subform (a sub-sub-subform perhaps) to enter the results.

OK, I'll have a play around with that!
Of course not. You would use a Combo Box to allow the user to *SELECT* the
test, not require them to type it at all.

Again: if you're trying to set up One Grand Master Query to do everything,
you're on the wrong track! Access Forms are *very* powerful and provide all
the tools you need to get this.

I do have one grand master query, but I don't actually use it for anything-
it's still there from when I was teaching myself how to make queries I think!
My other queries relate to the kind of information I know I need to extract
(ie test results less than a certain value, for example)

Please explain. "Sort" means "to put records in a particular sequential
order". If you're working on entering data for a specific patient, what
difference does the order of the *other* records in the table (that you don't
see onscreen) make?

This is what I mean, for our purposes the case number is sequential by
patient and by day, so for data entry, knowing the last case that was entered
means that you know exactly where to start entering the next set of records.
If you want to base your mainform on a query of the Cases table, sorted by
case number, that works too. You have *a lot of choices* - mine was a
suggestion; if it doesn't fit your needs, make another choice!

This was exactly where I was having problems and thought I was making a
mistake in its design.
I get jet database engine errors saying '...cannot find a record in the
table 'Patient Demographic Data' with key matching field(s) 'Hospital Number''
My translation of this was that it was looking for a patient who already
existed in tables, but was unable to create a new record for a new patient.

One possible concern: you don't actually say, but it sounds like you may be
working in table or query datasheets. DON'T. Datasheets are very limited;
Forms are much more appropriate.

It's ok, I am using forms - except for the sub-sub form (results) which is a
datasheet

karen
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top