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

Jack,

Just poor communication while writing past my bedtime.  The field names are
Drug, manufacturer.  The prescription number is  a sequential number from
the transactions table.

Shrink = I have 100 pills and while filling an order I drop 10 of them into
my soup.

Thanks,
Kevin


I'm no stranger, just a friend you haven't met...

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jack and Pat
Sent: Tuesday, October 28, 2008 1:51 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [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

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