medication table

[Robin via AccessMonster.com]

I have developed 2 tables. One is an inventory of sorts table with a
medication name, dose, lot number, and expiration date. There may be many
entries with same medication name and dose but the lot number will be unique.
The other table is one which patient names will be placed along with the
medication we are giving (samples), name, number of samples, lot number, and
expiration date. What I need is how to get the total number available of each
medication and dose (of all lot numbers) AND when I sign out a medication to
a patient, it will decrease the amount that was signed in under that lot
number and decrease the total amount. I.e. Jane Doe receives #30 of
Amoxicillin 500mg Lot #ABC. It will automatically decrease my inventory of
Amoxicillin Lot #ABC by #30 and decrease my total of Amoxicillin 500mg by 30.

Am I making sense? It is just two tables but I am a novice at access and
can't figure how to make the relationships or if I need to reformat my tables.
Any help would be greatly appreciated!!!

 

[PC Datasheet]

You need different tables:
TblMed
MedID
MedName

TblMedDose
MedDoseID
MedID
MedDose

TblMedDoseLot
MedDoseLotID
MedDoseID
Lot
ExpirationDate
MedDoseLotInventory

TblPatient
PatientID
FName
MI
LName

TblPatientMed
PatientMedID
PatientID
MedDoseLotID
DateGiven
NumberOfSamples

You need a form/subform for data entry. The main form is based on TblPatient
and the subform is based on TblPatientMed. Use a combobox to enter
MedDoseLotID. The rowsource property of the combobox is a query based on
TblMedDoseLot, TblMedDose and TblMed.

You will need some code in the AfterUpdate event of NumberOfSamples on the
subform to subtract from MedDoseLotInventory in TblMedDoseLot.

Don't record total inventory. You should calculate it where needed in a form
or report.

 

[Robin via AccessMonster.com]

Thanks so much for the response. Sorry I posted duplicate-wasn't able to
initially tell if it was posted. This is very helpful. Robin.

You need different tables:
TblMed
MedID
MedName

TblMedDose
MedDoseID
MedID
MedDose

TblMedDoseLot
MedDoseLotID
MedDoseID
Lot
ExpirationDate
MedDoseLotInventory

TblPatient
PatientID
FName
MI
LName

TblPatientMed
PatientMedID
PatientID
MedDoseLotID
DateGiven
NumberOfSamples

You need a form/subform for data entry. The main form is based on TblPatient
and the subform is based on TblPatientMed. Use a combobox to enter
MedDoseLotID. The rowsource property of the combobox is a query based on
TblMedDoseLot, TblMedDose and TblMed.

You will need some code in the AfterUpdate event of NumberOfSamples on the
subform to subtract from MedDoseLotInventory in TblMedDoseLot.

Don't record total inventory. You should calculate it where needed in a form
or report.

I have developed 2 tables. One is an inventory of sorts table with a
medication name, dose, lot number, and expiration date. There may be many

[quoted text clipped - 17 lines]

tables.
Any help would be greatly appreciated!!!

 

[PC Datasheet]

Contact me at my email address nelow if you need help with this database. My
fees are very reasonable.

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

Thanks so much for the response. Sorry I posted duplicate-wasn't able to
initially tell if it was posted. This is very helpful. Robin.

You need different tables:
TblMed
MedID
MedName

TblMedDose
MedDoseID
MedID
MedDose

TblMedDoseLot
MedDoseLotID
MedDoseID
Lot
ExpirationDate
MedDoseLotInventory

TblPatient
PatientID
FName
MI
LName

TblPatientMed
PatientMedID
PatientID
MedDoseLotID
DateGiven
NumberOfSamples

You need a form/subform for data entry. The main form is based on
TblPatient
and the subform is based on TblPatientMed. Use a combobox to enter
MedDoseLotID. The rowsource property of the combobox is a query based on
TblMedDoseLot, TblMedDose and TblMed.

You will need some code in the AfterUpdate event of NumberOfSamples on the
subform to subtract from MedDoseLotInventory in TblMedDoseLot.

Don't record total inventory. You should calculate it where needed in a
form
or report.

I have developed 2 tables. One is an inventory of sorts table with a
medication name, dose, lot number, and expiration date. There may be
many

[quoted text clipped - 17 lines]

tables.
Any help would be greatly appreciated!!!