Table structure....3 fields that maybe should've been 1

[kbrooks]

I have a database that is used to log surgeries performed. They had me create 3 fields for them to record up to 3 surgeries, as sometimes when they get in and are operating, another unplanned surgery is performed as needed. I set them up as:
Operation1Performed
Operation2Performed
Operation3Performed

Works fine when they just wanted an electronic printout to replace the handwritten log they kept before. However, now they're wanting reporting from the database and I'm not quite sure it's set up correctly.

Would it have been better to just have one OperationPerformed field, and (somehow.....subform, maybe?) allowed them to enter any number of operations on each patient?

TIA

 

[Kodo]

Would it have been better to just have one OperationPerformed field, and (somehow.....subform, maybe?) allowed them to enter any number of operations on each patient?

yes. You could have made one field and use a lookup table for the types.

 

[Mile-O]

Would it have been better to just have one OperationPerformed field, and (somehow.....subform, maybe?) allowed them to enter any number of operations on each patient?

It would be better to have none in that table. Create a new table for operations and include a new field (a foreign key) that is the primary key of the table you already have these in. Use a subform to display the operations.

 

[Kodo]

It would be better to have none in that table. Create a new table for operations and include a new field (a foreign key) that is the primary key of the table you already have these in. Use a subform to display the operations.

why would you want to do that when you can have one field in the primary table with a foreign key to a lookup table. Unless that is what you're saying and I'm just not understanding you.

 

[Mile-O]

Basically, I'm saying it's a many-to-many relationship and requires a junction table.

 

[Kodo]

Basically, I'm saying it's a many-to-many relationship and requires a junction table.

ah..ok, I see what you're saying now. Went back and re-read his description and this makes all the difference

as sometimes when they get in and are operating, another unplanned surgery is performed as needed