Complicated Design Multiple Tables/Reports

[drharb]

Hello, I'm trying to create a database that does multiple tasks to the same data...aren't we all. Here's where I'm stumped.



I have a bag full of medications that we need to track the "name, quantity of, concentration of, expiration dates, quantity needed."

Each bag has 6 compartments to hold the various medications.

It is easier to inventory if I have each compartment have it's own form and table.... or is it? Can I have a main table with multiple forms that only update the medications for each compartment? (If they are only in alphabetical order you have to jump from compartment to compartment, or... search for each med listed as you go through each compartment)

The ultimate goal is to have two separate reports...one is an alphabetical listing of each med and it's expiration dates etc... and another which lists each compartment's contents and expiration dates so that you can use it to check the compartment against.

I know Excel does some of this better but I hate the interface of looking at tables and it can't sort and give me reports like Access. I've done some more complicated db's with Access in the past, so I'm kind of frustrated that I can't seem to figure this out. :-)

Thanks, Dave

[CJ_London]

It is easier to inventory if I have each compartment have it's own form and table.... or is it?

No - it isn't

Can I have a main table with multiple forms that only update the medications for each compartment?

you could, but no, not the best way to go

on the basis if what you have described it looks like you need three tables

1. bags
2. compartments
3. available medication

and one form (to include 2 subforms)

however to provide a more focussed answer, will need to know more about how your business actually works - what happens when a medication has expired? what are the bags used for? what happens when a medication is used? what does 'quantity of' and 'quantity needed' mean? etc

[RayMilhon]

I would have 1 form with 2 tables 1 for the medications and one for the compartments. There would be a field in the medication table to link to the compartments for example

Medications
MedicationID
Name
Quantity
Concentration
expirationDate
Quantity_Needed (what is this field for?) Not sure it's needed
CompartmentID

Compartment
CompartmentID
Description
Location

Then your form would have a combobox for the Compartments
1 through 6

and a listbox for the medications

The source of the listbox would be something like

Select  MedicationID,Name,Quantity,Concentration,expirationDate,Quantity_Needed
From Medications Where CompartmentID = " & cbo_Compartment

Then in the Change event of the compartment Combo box have the following

lst_Medications.requery

[ssanfu]

Are the medications in compartment #2 the same in each Bag? Or do they/can they vary?
Is it necessary to keep a history of inventories?



Another possible table structure:

tblMedications
--------------
MedID_PK           (PK - autonumber)
MedName            (text)
Concentration      (single)
Units              (text)  (vials, pkgs, ml, etc)
Quantity_Needed    (Integer)


tblBagInventories
------------------
BagInvID_PK        (PK - autonumber)
MedID_FK           (Long) 'link to tblMedications
BagNumber          (Integer)
CompartmentNum     (Integer)
Quantity           (integer)
Units              (text)  (vials, pkgs, ml, etc)
MedExpireDate      (Date/time)
InventoryDate      (Date/time)

[drharb]

Hi All, thanks for the swift replies. Now I have to decide which way to go, but for the sake of argument I'll clarify the questions you asked.

1. This is a medevac operation. We fly to remote villages and transport patients to higher levels of care.
2. When we use a medication or it expires we replace it with new meds, obviously, but we inventory the bags bi-weekly and assure the appropriate quantities are present and that they are not out of date.
3. "Quatity of" is just the par level that we keep in each compartment. The pharmacy doesn't always have what we need immediately, so we keep a running total of "quantity needed" so you know your compartment isn't up to it's par level.
4. The meds remain the same in all the bags and compartments.
5. We don't keep any history regarding inventories.