Help creating a database to manage expiration dates of medications

[sdrewel]

I am trying to create a database to analyze expiration dates of multiple products.



Let me lay out what I am trying to accomplish.

I have 5 ambulances, each ambulance has the same quantity of medications on it. There are three separate locations medications are stored on each truck. Each month I need to be able to update expiration dates, query what medications will expire by a set date and where exactly they are located.

SO here is the structure as I see it.

Ambulance 1 contains, a drug drawer with approximately 30 different medications, a trauma bag with about 10 different medications, and a blood glucose kit with 2 different medications. Now some of medications are repeated in the different locations, but have different quantities. The medications will never all have the same expiration date, and medication 1 might have 8 vials, where medication 2 may have 4 vials. I would like my form to be dynamic based on the inventory level of the medications. That way if we decide to keep 10 vials instead of 8 the form will then require the 2 extra data points.

The tables I have created so far...
table 1 lists all the ambulances
table 2 lists medications in the drug drawer and the inventory level
table 3 lists medications in the trauma bag and the inventory level
table 4 lists medications in the blood glucose kit and the inventory level

I need to create a table to store my expiration dates for each of the locations in each of the ambulances
I need to create a form to simplify the entry of the data into those tables and I want the form and table to be dynamic to the inventory level.

Any help is appreciated.

[burrina]

Here is an excellent link to Data Modeling. http://www.databaseanswers.org/data_models/

HTH

P.S. Sit down and draw out what you want on paper just like an Architects drawings. You may have to modify it, but the better plan you start with, the less problems you will have later on.

[ranman256]

I would have 2 tables
table 1 lists all the ambulances
table 2 that holds all medication, in all ambulances , in all compartments.

[TBL2 flds]
ambID
drawer (be it: trama bag, drug drawer, glucose kit)
drug
exp date

the form can then filter (using one table)
an ambulance, or 'drawer' or drug.

[burrina]

What is a Drawer? I would think you need a table just for the Drugs and also possible a table for Drawers. (Compartment ???)