Running Inventory

[Kazdagi]

Ok, I thought this would be easy enough but the answer keep avoiding me.

I work in a small rural clinic and we get a lot of sample medications that
we provide to folks that cannot afford medicines. For about 3 years now I
have watched managment mismanage this inventory. Paper everywhere, notebooks
filled with reinforced holes in sheets that are folding over and driving me
crazy. There has got to be a better way of tracking this maddness . . .
Creating a spreadsheet to manage the meds is not hard, nor is one to track
meds dispensed to patients. What I am trying to do is create something that
has a accurate running total of meds in house. Say if I give 30 tablets of
drug yuckie to a patient I would want 30 subtracted from drug yuckie total.
Sounds easy but is totally perplexing to me. I have tried two pages - first
page with labels such as Name of Med., Strength, Lot #, Exp. Date, # of
tablets. Second page labels include date, Chart #, Name of Medicine,
Strength, Lot #, Exp Date and # of tablets dispensed (which I want to
subtract from the original number of that medicine and refresh the total to
reflect the new value. And I can't do it. Help and direction is what I need
- and perhaps someone that finds this very easy and interesting. I hate
seening thes binders and stacks of papers cluttering up workspace. . . there
must be an esier way! Thanks again,

Kazdagi

 

[Billy Liddel]

Kazdagi

I did not fully read your post before dashing off this simple stock.

Sheet2:

Date Medicine N In N Out Overall Total
02/01/2007 Aspirin 200 200
02/01/2007 Codeine 150 350
03/02/2007 Aspirin 25 325
04/02/2007 Aspirin 50 275
04/02/2007 Codeine 20 255
04/02/2007 Aspirin 50 275
04/02/2007 Codeine 20 255

Select a good range below and created range NAmes, Insert, Names, Create.
In B2 typed =SUM($C$2:C2)-SUM($D$22) to give the overall Stock. Copy down
through the named range.

Sheet1 (Report)

In B3
Stock Levels
Aspirin 125
Codeine 130

In c4 type
=SUMPRODUCT(--(Medicine=B3)*(N_In))-SUMPRODUCT((--(Medicine=B3)*(N_Out)))

and copy down.

Can you work with this or would you like more detail. I'd also check with
your accountant that this method is OK before dispensing with the paperwork
and ledgers.

Regards
Peter A

 

[Billy Liddel]

Kazagi

I thought a bit more about your problem. I think that it is sit down with a
pen and paper time. What do you want out of the system and how much time is
there to input the data.

These are the sheets I came up with
Medicines
Batch#
Strength
Date in
Exp.Date
Order#
Qty
Name
===
Supplier
Manufacturer
Address1
Address2
Town
Country
PostCode
Tel#
Fax#
===
Patients
DOB
Fname
Lname
Address
Sex
Next of Kin (NOK)
PatientID
=====
Dispensing
Date
PatientID
DrugName
Qty
Strength
====
Then you would need your stock report something like my earlier reply.
Do you want a Patient history? You could get one from the above tables.

Of couse this would ideally be done on a database, but you can copy Excel
Tables into Access sometiome in the future.

Regards
Peter A

 

[Kazdagi]

Peter,

First let me say thanks for your time in helping me with my problem, I truly
appreciate it. I took

your advice and wrote out what I needed to accomplish. Using some of your
suggestions this

would seem to do what I want. BTW I also have Access and don't really know
how to use it

any better than Excel - but I do have it if it will be easier.

What I need is something that allows exact count of pills that are in the
clinic, per type. This

would change each time we dispensed samples, say like giving 30 Lipitor 10mg
to Ms. Jones

would change the total of 300 to 270. Simple, right? I don't know about
that . . .

=====

Medicines
Name Lipitor
Strength 10mg
Batch# 12345
Exp.Date 02/2008
Qty 300
Class Statin
Category Cholesterol-lowering

=====

Patients
Date 07/23/2007
Chart # 1098765
Fname Wilma
Lname Flintstone
DOB Long ago
Next of Kin (NOK) Fred
Medication Lipitor
Batch# 12345
Exp. Date 02/2008
Qty Dispensed 30

=====

Now - other patient info will be found in chart. Use of database would
allow easy

recall/notification of patients should medicine be black boxed or pulled
from market (Vioxx was

yanked awhile back and had us pulling our hair out flipping back through
paper records trying

to determine who we had given any to). The 'in stock' count of medicines
allows us to

control/monitor and also may provide police/government agencies with needed
info should there be need, i.e. theft or breakin.

Time to build and implement is not a real concern as that we would be
starting fresh without having to input old records. I just want it simple
and effective.


It seems so easy in my mind but I can't seem to get it to work. I need to
tie the 30 I gave

Wilma, probably by batch# and exp. date (because different drug companies
may have same

batch#'s but it would be highly unlikely they would also have same exp date)
to the original

300, perform my calculation, arrive at new total and be ready to change that
total the next time

I give away some lipitor 10mg with same batch# and exp. date to then again
arrive at yet

another new and correct total number of Lipitor. Crystal clear, right? It
should be easy, maybe

I am trying to make an omelette when all I need is scrambled eggs? I do
have Access ;-)

Thanks again,

Craig C.

 

[Billy Liddel]

Craig

I'll have a look at this in Excel, it is an Excel Forum and it's years since
I did anything on Access.

My e-mail address is peter_athertonAThotmail.com. Do the obvious with the
AT I'll send you a spreadsheet (when its done) for you to pass comments.

Regards
Peter

 

[Bob Phillips]

ah, the days of reinforced punched hole ... I remember them well.

Have a summary sheet with the names of the medicines in A2:An, then in B2

=SUMIF(Sheet1!A:A,A2,Sheet1!E:E)-SUMIF(Sheet2!B:B,A2,Sheet2!F:F)

where Sheet1 is the stock list sheet with the name in A and E the num,
Sheet2 is the dispensed sheet with name in B and F the num. Then copy down.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

My e-mail address is peter_athertonAThotmail.com. Do the obvious with
the
AT I'll send you a spreadsheet (when its done) for you to pass comments.

Billy Liddell - the old Liverpool winger?