Simple Inventory tracking for a Pharmacy

[jculp123180]

Hi guys,

I'm new to this board, I just found it while trying to Google some how-to information for Access 2007. I took a class in Access like 10 years ago and thought I could remember enough to use it now...but I don't.

I'm running a small mission hospital in southern Sudan, and I currently use Excel to keep a running inventory of our Pharmacy. I'll attach that spreadsheet in case it's helpful. Right now I just have a current inventory of each medication we carry, and I track average monthly usage so I know when I need to re-order.

I'm leaving in two months, and I'd like to turn the pharmacy tracking over to some of my Sudanese friends, but since most of them are pretty new to anything involving a computer, it's not going to work trying to show them how to update formulas for averages each month and all of the other complications that come with Excel, so I'm trying to put together something in Access that will require very little manipulation from them. It seems simple, when we get in a shipment they need to be able to enter how much was received, and at the end of each day they need to enter how much was used.

Right now the pharmacists keep a paper on the counter, and every time they hand someone some medicine, they write down the number. At the end of the day I get a page with lines that look like this "Amoxicillin 500mg: 10, 6, 5, 20, 18...etc." I then add the numbers together and enter them in the spreadsheet. I'd like it to be like that with the Access database. At the end of the day, one person enters the total amount used for each drug into a form and the inventories are updated. Then some kind of report can show average monthly (or daily, which I think might be easier) usage and give the amount of months/days until we're out of each drug. It simply tells us how much to re-order.

I thought this would be simple, but I'm realizing how much I don't know about Access. So far I've imported a list of meds we carry, and made a form to add a new medication to the inventory, but I'm dumb on how queries work so I think my work is just sloppy. Can someone help steer me in the right direction? I'll attach what I have in Access also so you can laugh at me

Thanks!

 

[jdraw]

I have acc2003 so can not open an accdb. Have you looked at these data models to get some ideas? There are several, but this one deals with inventory. See
http://www.databaseanswers.org/data_models/inventory_control_for_retail/index.htm

 

[jculp123180]

I'll save it as 2003 and upload it here. The more I check out the "This link might help you" responses, the more I realize how much I just don't know.

 

[spoole]

OK lets start you off.

On a very basic level you will want 2 Tables and 1 form.

Table A has 3 fields:

1. Medication (e.g 'salbutimol')
2. Dose (e.g '500')
3. FORM (e.g 'cream')

Table B has 5 fields:

1. Date (e.g '01/01/2001')
2. Qty (e.g '10')
3. Medication (e.g. 'salbutimol')
4. Form (e.g 'cream')
5. Dose (e.g '500')


Form A will have 3 combo boxes, 1 date box and 1 Quantity box and will update/add to Table B

1. Medication (combo box based on Table A)
2. Form (combo box based on Table A)
3. Dose (combo box based on Table A)
4. Date (either use an inbuilt access function or create an input mask)
5. Quantity (manually entered number)

Ok now lets rewind a little. A good way of designing a DB is to assume that the user will make mistakes, a little harsh I know but in reality it holds true. Based on this assumption you want user input to be a minimum. So Table A is your products table, you will use this table to create combo boxes on an input form(reducing risk of error or fat fingers), the best way is the user selects 'Medication' from a drop down box and this in turn will limit the values in the 'dose' combo based on the value chosen in the 'medication' combo box and so on for a 'form' combo box (search for basing one combo box on another)


Now its simply a case of storing the values on the form into Table B.

Now go forth my friend and design the best database a man possible can!



*As this is a DB regarding medication You may also want to do a search on 'Form Validation' and 'Normalisation'

 

[jculp123180]

Thanks Spoole. How do I go from getting those values stored in Table B to seeing the actual remaining inventory? I have forms and tables made, the place where I'm running into a problem is actually updating the information. I do agree with you that the number of errors should be limited. That's why I'm going from Excel to Access...the guys that I'm showing how to do this barely know how to check email, let alone deal with something like this.

 

[spoole]

I may have time this morning so it may be easier if I knock something up for you and then go through the theory so you can develop it further. Don't hold your breath though as I have to do it in bits when the Boss is not looking

 

[jculp123180]

That would be awesome. Thanks!

 

[spoole]

OK here is the base DB. It is not pretty due to time and I have not done everything you need but there is enough for you to be able develop it into your own project as most of the theory is there.

The DB includes a form for dispensing, a startup form and a report of all dispensed by week and product.

I have added comments and points on the forms for you to think about and some things for you to do such as add the third combo box, and you need to think about how you are going handle on hand stock and deliveries etc. I also have added descriptions against all the current objects to help you understand what has been done so far.

Have a look around get yourself familiar with the tables/Forms/Reports so you know it works, and no doubt once you understand the workings of the DB you will have questions and ideas.

Steve.

 

[jculp123180]

Awesome, I'll check it out. Thanks!