create a database with multiple criteria for field

[dadavis9s]

Please excuse any wrong terms I may use when attempting to ask my question - I'm new to the community

My goal is to set up a database from where I can pull information, in any combination, to perform research, evaluate procedures, etc. Many of these procedures are done at the same time (i.e. in the same surgery setting), so the database needs to be organized and complete enough to allow for easy data manipulation/pulling.

Most of my data is a simple drop down selection menu (i.e patient's sex, type of anesthesia used, grafting locations, etc.) What I'm having trouble with are columns that need multiple combinations as well as manual data entries. For example, the column for "fillers" needs to have the option to select which fillers were used, where they were used, and manually enter the volume of how much was used. Multiple areas and multiple fillers can be used in the same session.

Types of fillers: Voluma, Volbella, Vollure, Juvederm, Restylane Lyft, Restylane-L, Restylane Silk, Restylane Refyne, Restylane Defyne, Sculptra, Radiesse

Locations: liquid rhinoplasty, neck, perioral, lip, Temples, upper eyelid, Lateral SOOF, Medial SOOF, Deep Malar, Superficial Malar, nasolabial fold, marionette lines, chin, lips, earlobe, jawline

Volume: manual data entry

Does anyone have any suggestions on how this can be done effectively and look clean?

[June7]

Conventional approach would be a related dependent table that would have a record for each filler used during a surgery.

Might be able to have a single table for all data pertaining to a surgery, something like:

SurgeryDetails
SurgeryID_FK
ElementID_FK
Volume (filled in only for elements that require it)

That would require a single source table for all surgical elements:

SurgeryElements
ElementID
ElementType (Filler, Location)
ElementName (Voluma, etc, neck, etc)

With this structure, cascading comboboxes would likely be beneficial. User selects ElementType in one combobox and another combobox ElementName list is filtered by first combobox. However, be aware this does not work nicely on form in Continuous or Datasheet view when combobox displays text alias.

[dadavis9s]

I'm more-so looking to be able to select a combination of options, and then manually enter numerical data that corresponds to the data I selected. For example, for one patient I may need to enter data that says "this patient received X fillers in their lip at a volume of XYZ, as well as Y fillers in their upper eyelid at a volume of ABC." I'd like the filler type and location to be able to be selected from something like a drop down menu, but the corresponding volume of each used would need to be manually entered. Hopefully that makes sense! I've included below the entire scope of the project that I'm working on in hopes that that relays a better understanding of the database that I need to create.


The goal is to set up an excel spreadsheet that can serve as a database from where I can pull information, in any combination, to perform research, evaluate procedures, etc. Many of these procedures are done at the same time (i.e. in the same surgery setting), so the database needs to be organized and complete enough to allow for easy data manipulation/pulling. This will serve as a data mine for my career. I don’t need nitty gritty details, because as long as I can identify the patients of interest, I can go into their charts at that point.

Patient Name
Patient Sex
DOB
MRN
Anesthesia

-General

-Local

Microfat grafting

-location and volume for each location

-locations include: Temples, upper eyelid, Lateral SOOF, Medial SOOF, Deep Malar, Superficial Malar, nasolabial fold, marionette lines, chin, lips, earlobe, jawline

Nanofat Grafting

-location and volume for each location

-perioral, lower lid

Lower Blepharoplasty

-Skin-muscle

-Transconjunctival

-skin only

Upper Blepharoplasty

-Skin-muscle

-skin only

Canthopexy

-deep

-superficial

Facelift

-primary

-secondary

-Extended Deep plane

-SMAS Plication

NeckLift

-primary
-secondary
-liposuction central neck
-Open Platysmaplasty

Browlift

-Lateral
-Total

Rhinoplasty
Buccal Fat pad removal
Earlobe reduction
Lip lift
Peel

-TCA
-Croton Oil

Fillers (need to be able to pull which filler(s) were used and for which area—multiple areas and fillers can be used in same session, and volume of each used)

-Voluma
-Volbella
-Vollure
-Juvederm
-Restylane Lyft
-Restylane-L
-Restylane Silk
-Restylane Refyne
-Restylane Defyne
-Sculptra
-Radiesse

-locations: liquid rhinoplasty, neck, perioral, lip, Temples, upper eyelid, Lateral SOOF, Medial SOOF, Deep Malar, Superficial Malar, nasolabial fold, marionette lines, chin, lips, earlobe, jawline

Neuromodulators (location and units for each location:

-Botox
-Dysport

-Locations: frontalis, corrugator, procerus, orbicularis oculi, DAO, mentalis, Platysma, Masseter, orbicularis oris

Hyaluronidase

-yes

-amount

-no

Conventional approach would be a related dependent table that would have a record for each filler used during a surgery.

Might be able to have a single table for all data pertaining to a surgery, something like:

SurgeryDetails
SurgeryID_FK
ElementID_FK
Volume (filled in only for elements that require it)

That would require a single source table for all surgical elements:

SurgeryElements
ElementID
ElementType (Filler, Location)
ElementName (Voluma, etc, neck, etc)

With this structure, cascading comboboxes would likely be beneficial. User selects ElementType in one combobox and another combobox ElementName list is filtered by first combobox. However, be aware this does not work nicely on form in Continuous or Datasheet view when combobox displays text alias.

[pbaldy]

Post 3 was moderated, I'm posting to trigger email notifications.

[June7]

Not seeing how that modifies my previous comments.

[dadavis9s]

Not seeing how that modifies my previous comments.

Forgive me as I'm not extremely familiar with Access. I tried your suggestion of making a cascading combobox. It appears to have "worked" based on the YouTube tutorials I've watched. The problem that I'm still running into is that I cannot select multiple data from a cascading combobox the way I could with a regular combobox. I also haven't found out how to enter the numerical value of the volume for each entry. So if a patient has voluma filler on their lips in a volume of 0.05 mL, and that same patient in the same session gets juvederm on their chin in a volume of 0.03 mL, I dont know how to reflect that using the cascading combo boxes.

[June7]

Cannot select multiple data from a regular combobox. This requires a combobox bound to a multi-value field. I never use multi-value field.

Each filler would be its own record. Why would cascading combobox have any bearing on entering volume? Type a number into volume field.