Simple Inventory Database

[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 :P



Okay so I just tried to upload the documents and they're both too big. Here's the Access db (which is editable):

https://docs.google.com/leaf?id=0BzW...thkey=COOgjM4C

And here's the Excel spreadsheet I'm currently using:

https://docs.google.com/leaf?id=0BzW...thkey=CMqdjd0P

Thanks!

[crazycat503]

hey..

ve u done ur project? I have done a similar program for an eye hospital and may be could help you out. let me know, will ya?

[jculp123180]

I haven't done anything yet. When I posted this I was stuck and I still have no idea what to do yet. Thanks!

[alansidman]

Did you check out my response at this forum

http://forums.aspfree.com/microsoft-...se-441456.html

Alan

[jculp123180]

I did, I actually found that template when I was looking for solutions, but when I open it up I don't even know where to start trying to change it. All I need to do is keep track of what is on hand, and all of the example databases are way more complicated than what I need. A bunch of extra unnecessary stuff is just going to make it more confusing for the pharmacists when I show them how to use it.

[crazycat503]

hey!

sorry for been lost. I will attach my program for u to c tommorow at 12pm sudanese time. its small tho written in vb6 a while ago. They are quite similar and you can see the database as it is.

[crazycat503]

looking at your table, i dont get the use of inventory table. from my understandin (so that i can tweak my design for you): u got list of medicines in medicine list table. from that table, people can order amount. then when you are out of the medicine, u order (orders added). thats the process right?