Full Version: Storing One Form Value In Multiple Forms/ Basic Db Design
UtterAccess Forums > Microsoft® Access > Access Forms
jonaken
Hey Everyone,
I am quite new to access, so humor me if this is a crazy question.
I have a fledgling database that will be based on a number of existing paper forms. I am trying to figure out how to best split all of it into tables in Access 2007. The database handles patient information and uses an (existing) unique number to reference each patient. I was hoping to use that as a key field in a lot of different tables.
When a patient gets added to the database I am creating, things get more complicated. There is one main paper form (call it form 1) that gets filled out every year that the patient receives support and is the biggest and most complete form. Unfortunately, since the work is done in the field, there is not always time to fill out this form first. Sometimes, the patient receives support before that paper form is filled out. The support is managed with a different paper form (let's call it form 2).
This complexity leads me to the following: 3 tables and 2 forms... one baseline table (tblBaseline, let's say) that includes only the number code of the patient and his name and one each for paper form 1 (tbl1) and paper form 2 (tbl2). I would like to be able to have one access form that puts information into tblBaseline and into tbl1. The idea would be that you could mostly leave the tbl1 fields empty until that paper form was filled out, but you would at least get the essential information into the all-important tblBaseline. Ideally, the same form could be used to input more than one year's "form 1" while keeping the tblBaseline indexed.
In the case that we have "form 1" from the start, I would need to have my access form put at least the number code into tblBaseline as well as into tbl1. Is there some way to do that?
In the case that we don't have "form 1" from the start, we would have to input the patient number and name in the same access form, but make sure that we don't create duplicate entries in tblBaseline when we do finally input "form 1"...
I guess I am trying to avoid the redundancy (cough cough simplicity) of having to input a patient name and twice. Maybe that inconvenience is the key to my problem, but it would be more user-friendly if it could be done with just one form.
Like I said, I am a novice and might be going at it completely wrong (or this explanation might be confusing). Any ideas?
Thanks a lot.
J
theDBguy
Hi J,
welcome2UA.gif
When designing a relational database (whether using Access or not), it is very important to get the table structure correct first before designing any forms to enter any data. If you are not familiar with "Normalization" rules, take a look at this Wiki article to get started: Normalizing A Database.
You can also take a look at Crystal's Access Basics tutorials to get some basic ideas on how to design an Access database.
Just my 2 cents... 2cents.gif
Jeff B.
First, welcome2UA.gif
If this is your first experience using a relational database, I'm afraid you may have some rather steep learning curves ahead ...
Even though Access tables may look like spreadsheets, if you know Excel, you'll probably have to UN-learn some things to get good use of Access.
... and based on your description, you appear to be starting with the forms (e.g., your existing paper forms). In Access, you'll want to start with the data. Turn off Access, grab paper and pencil, and sketch out the things about which you want to collect data (i.e., the "entities"). Then draw lines showing how those entities are connected to each other ("relationships"). Now fill in the pieces of data that belong to each entity.
After you've done that (and brushed up on relational database design and normalization), turn Access back on and start designing your tables. ... your forms come AFTER the tables!
Good luck, and keep posting back with questions.
jonaken
Thanks DBguy and Jeff,
Reading a bit more, it is obvious that I was getting way ahead of myself. Thanks for the links. More questions later, no doubt...
theDBguy
Hi J,
Jeff and I are happy to assist. Good luck and happy reading.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.