how tables can i use on a single form and how is it done

[mikeinohio]

I am building a database that will contain patient information that has to
be broken down into 14 different tables because of the amount of information
that is required by law to collect, it all will not fit into one table,
my delima is how can i have a form that i can input all this data in this
data based on a patient ID number in a single form, or how can i have it
where i can move ot each form and yet still be entering information for the
ssame patientfor each table. and how can i set up a form to insert a picture

 

[John W. Vinson]

I am building a database that will contain patient information that has to
be broken down into 14 different tables because of the amount of information
that is required by law to collect, it all will not fit into one table,
my delima is how can i have a form that i can input all this data in this
data based on a patient ID number in a single form, or how can i have it
where i can move ot each form and yet still be entering information for the
ssame patientfor each table. and how can i set up a form to insert a picture

STOP.

You are *on the wrong track*.

Designing your tables on the basis of paper forms, or "what is required by law
to collect", is going to give you incorrectly normalized tables for sure!

Read up about Normalization on the links from these websites:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

You'll need to identify the logical relationships among the data that you need
to collect and base your tables *on those relationships*. One patient will
indeed have data in many tables... but perhaps not the tables you're currently
contemplating.

That said, you can use Subforms (dozens of them) on a mainform to link tables
together.

Note that any database containing patient data must comply with the very
stringent HIPAA patient-privacy regulations. Some would argue that these
regulations would rule out the use of Access altogether, as it is
insufficiently secure. I'd strongly suggest getting professional advice from
someone familiar with HIPAA unless you have a few million bucks available for
fines and legal costs.

John W. Vinson [MVP]

 

[mikeinohio]

I have read where as long as the database can be:

1. set up to log a user out after a set amount of time of idleness
2. store on a secure server using (2003 Server or better)
3. be set up that only particular users can ses certain info

I believe Access can provide that.

But as far as the subform displaing the other info does that mean on each
table has to have a field containing the patient ID number as a foriegn key?
or should i Consider an alternate way of doin things? like another program?

 

[John W. Vinson]

I have read where as long as the database can be:

1. set up to log a user out after a set amount of time of idleness
2. store on a secure server using (2003 Server or better)
3. be set up that only particular users can ses certain info

I believe Access can provide that.

If you are indeed using a secure SQL/Server instance to store the data, and
provide appropriate secure links to the Access frontend, you may be ok... I'd
still want an opinion from a HIPAA experienced developer though. I'm not one.

But as far as the subform displaing the other info does that mean on each
table has to have a field containing the patient ID number as a foriegn key?

Absolutely. Of course that imposes no added burden on the user - the subform
fills in the ID automatically.

or should i Consider an alternate way of doin things? like another program?

Well, that's up to you. Access (with a secure SQL backend) is probably a very
good choice for this application, based on what little you've posted, though.

John W. Vinson [MVP]

 

[mikeinohio]

ok i understand, i will look into how to set up a secure sql backend. one
more question, a lot of the information is mostly check boxes will that be a
problem with the use of subforms?

 

[John W. Vinson]

ok i understand, i will look into how to set up a secure sql backend. one
more question, a lot of the information is mostly check boxes will that be a
problem with the use of subforms?

If you have *fields* with data (medical conditions, medications, insurers or
the like) as fieldnames, you're on the wrong track. This is a common problem
from designing a relational database to fit an existing paper form, but it's a
sure route to trouble. What are these checkboxes?

John W. Vinson [MVP]

 

[mikeinohio]

The check boxes just indicate if a particular medical condition exist, i
solved that problem with the use of subforms and placed them on a tab
control. and you are absolutly corret recreating a paper form in a database
can be a nightmare, i will keep you posted and far as the security issue so
that you can be informed as well, but from what i have read i will be
ok.....

Thanks John

 

[John W. Vinson]

The check boxes just indicate if a particular medical condition exist, i
solved that problem with the use of subforms and placed them on a tab
control. and you are absolutly corret recreating a paper form in a database
can be a nightmare, i will keep you posted and far as the security issue so
that you can be informed as well, but from what i have read i will be
ok.....

You still will want to revise the structure of your database. What if you
decide that you must include a new medical condition? Now you'll need to
revise your table structure, change all queries that use the table, change the
structure of your form and all your reports... a nightmare. A normalized
design will let you just add a new record in the Conditions table... and
you're DONE.

John W. Vinson [MVP]

 

[mikeinohio]

so what you saying is design the tables in such a way that it minimizes
the amount of fields you need for example one criteria area has 6 differnt
choices so instead of making these choices individual fields, just select the
desired choice from a pulldown instead, let me know if i am on the right
track

 

[John W. Vinson]

so what you saying is design the tables in such a way that it minimizes
the amount of fields you need for example one criteria area has 6 differnt
choices so instead of making these choices individual fields, just select the
desired choice from a pulldown instead, let me know if i am on the right
track

Well... don't put the pulldown *IN THE TABLE*. Just use a foreign key in the
table; and use a Combo Box (a "pulldown" by its proper name) on the Form that
you use to enter the data.

Other than that... exactly. Again, fields are expensive; records are cheap.

John W. Vinson [MVP]

 

[mikeinohio]

Right, i meant on the form itself and not in the table, i know that breaks
one of the rules of database design i believe. Thanks for being so
patient....