[AccessD] Many to One to Many to Many form design problem

Kevin,

Just some initial observations that may help in your database and
application design.

You mention "medications" but you only define "Drug".
You mention "Supplier" but reference "Manufacturer"
You mention Patient, but I see no reference to "Prescription"

Perhaps there's more than 1 type of transaction. I can see possible
transactions for --
 drugOrdered, drugReceived, drugDispensed

I am not familiar with Drug Shrink

jack

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kevin Waddle
Sent: Monday, October 27, 2008 11:00 PM
To: AccessD at databaseadvisors.com; ACCESS-L at PEACH.EASE.LSOFT.COM
Subject: [AccessD] Many to One to Many to Many form design problem

Hello,

I am hitting the wall on a form design. 
The database is supposed to track medications dispensed by a doctor's
office.
There are four tables:
tblPatient
-       PatientID           - PK
-       PatientName
-       Etc
tblDrug
-       DrugID              - PK
-       Drug Name
-       Strength
-       Type (Capsule, Liquid, etc)
tblDrugLot
-       DrugLotID        - PK
-       DrugID           - FK related to tblDrug
-       Lot Number
-       Manufacturer
-       Expiration Date
tblTransaction
-       TransactionID    - PK
-       PatientID        - FK related to tblPatient
-       DrugID           - FK related to tblDrug
-       Transaction Date
-       Units Ordered
-       Units Received
-       Units Shrink
-       Units Dispensed


A transaction can be medication coming in from the supplier or going out to
a patient.

I am trying to build a transaction form that will allow me to identify not
only what drug, but what lot, came in or went out.


TIA,
Kevin 


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com