Organizing the tables right?

[mxp346]

I have a paper form that i'm trying to convert the information into a database using Access. The form has 3 main categories with check lists underneath them. An abreviated example of what it is like is below...

Type of Anesthesia
     1.Epidural   2.Spinal   3.General    4.Other

Procedure
     1.D&C   2.Tubal Ligation   3.C-section    4.Other

Delivery Events
     1.Spinal   2.Catastrophic   3.General    4.Other

I understand how I would do it if the person could check one item under each category. I just set up an Occurrence table with fields for each of the categories and then i could have the person just enter the corresponding number. The primary key of the Occurrence table is just a autonumber to keep track of the occurrences. Although it doesn't happen often, the person could check off more than 1 item under a category. I don't know if i should have multiple numbers seperated by a comma entered into one field for the category. Or if there's a better way to do it. Any suggestions on the best way to set this database up???

 

[dawnd3]

You will need to create junction tables. So if you have a main table that you now link to each of the other tables, ie a table of Proceedures, you will now need a table that stores the id's for both the main table and the proceedure tables. This way, each form can have more than one proceedure. Do this for each of your items. Does this help?

Dawn

http://www.calliopeconsulting.net

 

[mxp346]

Thanks for the help. I understand the idea what you're saying but i'm not sure how to implement it exactly.

tblOccurrence         tblOccProc            tblProcedure
*OccID                   ????                  *ProcID
 ProcID                  ????                   ProcDesc
 Date
 Etc...

Would I put OccID and ProcID in the table tblOccProc? Would I put anything else? So this would take care of the problem of having multiple ID's selected. Also on the form would i make several text boxes for ProcID's to be entered or would I just have one text box and have the person enter all of them into the one text box. Thanks again for the help

 

[dawnd3]

Hi again, Yes you would put both ids in the center table. Together they would both form the primary key. You would only put other fields in that table if the information was a fact about both keys. i.e. time spent on a particular Occurance/Procedure combination (the 2nd normal form) As far as the relationships, it should indicate that "One occurance can have many proceedures and one proceedure can belong to many occurances...so you would have a 1 to many relationship on each side with the junction table being the many side of each.

Once you set this up, you are going to create subform that has a combo box with the procedures available. The subform will be placed in your occurance main form (linked by the occurance ID) and each time the user selects a value an new record (combo box) will be presented to enter the next value, until complete.

Does this help at all?

Dawn

http://www.calliopeconsulting.net

 

[mxp346]

That is very helpful, thank you very much for the help Dawn.