how to link databases

[Robin via AccessMonster.com]

I have developed 2 tables in excel. One is an inventory list of medications
with name of drug, dose, and lot number, number of samples, etc. The second
is when samples are signed out with the patient name, name of drug, dose, lot
number, etc. What I want is a total of the drug and dose on the first table
regardless of the lot number. When I enter the patient I want the number of
samples to be removed from the lot number on the inventory list and the total
number available to decrease by that number. I.e. Amoxicillin 500mg #30 of
lot #abc. Total number of all Amox 500 is 200. When I enter this in the table
for patients, it will remove 30 from the 200 so my total is 170, but will
specifically remove it from lot #abc of all the amox 500. Make sense?? Help.
Thanks, Robin.

 

[Ed Warren]

You might want to consider putting your tables into a normalized data
structure in access

----------------------------------------------------------------------
Each drug comes in one to many doses
each drug does comes in one to many lots
patients take one to many sample from one to many lots.
------------------------------------------------------------------------

So we have (one possible structure), there may be (probably is) a better
organization if one knows the details of the business process. Example if
you receive all of a lot number at one time, If the number tracked is number
of samples or number of pills (10 samples may be 1000 pills)

Patients (PatientID, firstname, lastname, address, other demographics
Drug (drugID, DrugName)
Lot (LotID, DrugID, DoseUnits (MG, ML, etc), DoseValue(50, 100,500),
LotVendorID, NumberSampleReceived, etc)
Perscription (date/personid/lotid/number issued)

Now you can ask and report the following type questions.
I want a list of all the patients issued Amox in the past six months, along
with a count of how many patients were issued samples of each dosage.

Ed Warren

 

[PC Datasheet]

I answered your this same post of yours in another newsgroup yesterday. Why
are you posting again?

 

[Robin via AccessMonster.com]

Thanks for your answer. I wasn't able to find the other post in the other
group! I thought my computer had not sent it!! Thanks again. Robin.

I answered your this same post of yours in another newsgroup yesterday. Why
are you posting again?

I have developed 2 tables in excel. One is an inventory list of medications
with name of drug, dose, and lot number, number of samples, etc. The

[quoted text clipped - 15 lines]

Help.
Thanks, Robin.

 

[PC Datasheet]

Look in Microsoft.Public.Access.TableDesign on 10/3 for "Medication Table".

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Thanks for your answer. I wasn't able to find the other post in the other
group! I thought my computer had not sent it!! Thanks again. Robin.

I answered your this same post of yours in another newsgroup yesterday.
Why
are you posting again?

I have developed 2 tables in excel. One is an inventory list of
medications
with name of drug, dose, and lot number, number of samples, etc. The

[quoted text clipped - 15 lines]

Help.
Thanks, Robin.