Help with Database Design: Cross Post for Medical Database Build

[ssanfu]

That is the idea. For example, the table tblDUR. You would remove all fields that are drug names and have one field (a FK field) to another table that you store the drugs used/selected (Maybe tblDUR_Details / tblUDS_Details) with a third table that has the list of drug names (tblDrugs). This is a many-2-many relationship.

"tblDrugs" could have a PK field (autonumber), DrugAbbrev (Text), DrugName (Text), bDUR (Yes/No) and bUDS (Yes/No).

The fields bDUR and bUDS ("b" is for boolean) are so that you can choose which drugs would be available for selection (using a combo box) in each of the sub forms (DUR and UDS) when adding records.

[jrichardson10]

Steve, I think I understand the concepts you've highlighted. I built the barebones of the tables you suggested (so much smarter than the way I'd instinctively build it). However, I'm looking at part of what you said:

one field (a FK field) to another table that you store the drugs used/selected (Maybe tblDUR_Details / tblUDS_Details) with a third table that has the list of drug names (tblDrugs). This is a many-2-many relationship.

My question thus becomes, in the tblDUR_Details, I have my PK field (auotnumber), but then the next field is... what? My first thought was a short text field that would effectively be a list that I don't have to sit there and type. But does it actually need to be a different field type? I have not created tblUDS_Details because once I get this one sorted, I want to build it on my own and see how well I do. It's basically the same thing just from a different physical data source so I think I can manage.

The fields bDUR and bUDS ("b" is for boolean) are so that you can choose which drugs would be available for selection (using a combo box) in each of the sub forms (DUR and UDS) when adding records.

Another rookie question, how do I tell it that I want to be able to select more than one option in a combo box? The combo box will drop down and present me with a variety of options, but how do I tell it I want "these four" or whatever subset? Not necessarily while I'm working in the form, but whilst designing the table. I've attached my meager progress.

[orange]

how do I tell it that I want to be able to select more than one option in a combo box

Use a listbox, not a combo.
Here's an article I found via Google.
Good luck.

[ssanfu]

In tblDrugs, I suggested having two Boolean fields to be able to select which drugs are available to DUR and which drugs are available to UDS. If both DUR and UDS will ALWAYS have the identical drugs available, then you could remove the two Boolean fields.

My question thus becomes, in the tblDUR_Details, I have my PK field (auotnumber), but then the next field is... what? My first thought was a short text field that would effectively be a list that I don't have to sit there and type. But does it actually need to be a different field type? I have not created tblUDS_Details because once I get this one sorted, I want to build it on my own and see how well I do. It's basically the same thing just from a different physical data source so I think I can manage.

The order of the fields in a table do not matter. However, I always have the PK field as the first field, followed by any FK fields, then numeric fields, text fields, etc. because it is easier to field a field if it is "grouped" by type.

Another rookie question, how do I tell it that I want to be able to select more than one option in a combo box? The combo box will drop down and present me with a variety of options, but how do I tell it I want "these four" or whatever subset? Not necessarily while I'm working in the form, but whilst designing the table. I've attached my meager progress.

You can't with a combo box. I would use a main form/sub form arraignment, with a combo box to select the drugs wanted. Would only have to type on the date.



I created the queries and sub forms... you should look at this dB
Look at the relationship window, the table tblDrugs, tblDUR, tblUDS, the sub forms and the row source for the combo boxes on the two sub forms.

Then use the Draft4 version and recreate what I did.