#  Other Applications & Softwares  > Access Tables & Databases >  >  Excel or Access for Inventory control

## Quakefire

Howdy,


I maintain the medication stock for an ambulance service. Previously I had set up a simple inventory excel sheet to what I thought would make my life a little easier. The spreadsheet does what I wanted it to, tracks up to two different expiry dates for each medication, amount of stock that is on hand, min/max inventory amounts with coloured indicator for ok, order soon, order now and how much to bring up to max stock. It will also show how much of my stock is expiring at the end of the month.

What I didn't anticipate was how much time it would take to keep this up to date, to the point where im back to just keeping track of most of it in my head. While the spreadsheet gives me a quick overview, I also lose any ability to keep track of changes or pull any data from this sheet. 

What I would like is an easier way of updating the info, keeping records of changes (be great if I have the option of putting in -2 for example to just decrease, or input a full count and compare), generate usage reports and order forms.

I have no idea if I can do this is excel or if I need to move on to access, excel I atleast have an idea how to use, access not so much. Attached is the spreadsheet I currently use, I had individual sheets for each ambulance, plus the drug locker but I have edited out the ambulances for now.


Thanks in advance for your insight


DanDrug Stock.xls

----------


## noboffinme

Hi Quakefire,

I would definitely suggest Access, especially if you're starting to notice problems.

I can help you set this up if you can answer a few questions for me.

Is the 'Stock Sheet' spreadsheet going to be the main sheet you look at & this sheet gets it's info from both the 'Drug Cabinet' & 'Expired' worksheets?

Let me know 

Cheers.

----------


## Quakefire

if I remember correctly drug cabinet should be the main sheet with it's info feeding the expired sheet. Stock sheet was just the as a printout for me to fill in as I took inventory.

Thanks

Dan

----------


## noboffinme

Hi Dan,

I've attached a quick example using your spreadsheets.

The expired sheet has become a subform inside the stock form.

You can scroll through the records & I've also created a report.

This is just to show you a few of the things possible.

Happy to help you build what you need.

Cheers Peter

----------


## Quakefire

Hey Peter,

That looks great! Now if I order stock (usually 10 of one medication with same expiry date and 4 or 5 other medications for example) or I do a full count, will this keep track of changes? When I originally looked at this (gave up on access pretty quick due to lack of knowledge) I thought I would need a table for each medication so each individual med would have an entry with its own expiry date. which would all feed into a "master" stock sheet showing me the total stock and upcoming expiry dates. 

I really like the drug tracker form, I think that is a great idea, especially with the comments section. Sorry if i'm not explaining myself clearly, I think that if I can get the database design down, then the rest I can figure out slowly (maybe)

Thanks, Dan

----------


## noboffinme

Hi Dan,

I can add a Data Entry form so you can add your orders as they come in.

This would then be added to the table that that the Drug Tracker Form reads.

I ran out of room to post more detail in the DB due to restrictions on the file size so if you're happy with the Access option, maybe you should move this to the Access part of this forum.

The forum supports all Office apps.

Let me know when you're there.

Cheers Peter

PS, time to buy a Dummies book on Access as that will help you understand the move from Excel much better.

----------


## FDibbins

Thread moved to access at OP request

----------


## noboffinme

Hi Dan,

I'll need the Ambulance data you mentioned & any other data you have to help design this db.

Can you explain how you get your data, what results you're mainly interested in & what reports you're most likely to need.

I also need to know how the 2nd expiry date works ie how can medicine have 2 expiry dates?

Thanks Peter

----------


## Quakefire

Hey Peter, 

The ambulance data itself can be left out, it's basically a copy of what is in the stock cabinet but since the stock numbers don't change (that truck will always have 2 vials of Gravol) they were there more for the expiry dates, which I now keep track of separately. 

My data comes in two ways, the first is when I do a full count of everything in the cabinet, recording how many of each medication and their expiry dates. Because when we order medications, we may not get every vial from the same batch, we may get three that expire in June, two for September and 10 for December, before I made due with recording the two soonest expiry dates when I did a full inventory.

Because all the items are consumable, I need a way to put items into stock as well as remove them.

Reports would be upcoming expiry dates and how much of an item expires then, the medications who's stock is currently low, unfortunately this is different for different medications, some we might stock upwards of 60 vials, where as others we only carry 4. The last would be historical usage, for example if I choose to look at the last 6 months of data what was I using the most of as this can be used to adjusting stocking levels.


I hope this is the information you need, let me know if there is anything else.


Thanks again

Dan

----------


## noboffinme

Hi Dan,

Here's the DB developed a bit further.

I've changed some of the data in the Drug Tracker table in order to get the reports to give me some results.

From now on think of your Spreadsheets as 'Tables'.

In Access, there are also 'Queries', 'Forms' & 'Reports'

'Queries' are Table data with various Filters/Parameters that answer some of the Questions you needed to know.

'Forms' use the fields of a Table to allow viewing of data & Data Entry.

'Reports' are just the Queries presented in a more presentable way. 

Can you have a play with this, enter some data, run some reports, use it as you would intendto use it as this will highlight what's wrong or missing.

Cheers Peter

----------


## Xx7

This allows you to update inventory quantities at the count date.

1) Go to the "Update Form" and enter the quantities (if no change from last count, leave blank)
2) Click the "Update" button
3) Quantities will be updated in the "Inventory" TAB
4) "Inventory" TAB keeps track of quantities at every count.

----------


## Quakefire

Thanks Peter, this is a great starting point for me. I'm going to spend some time reading up on access to try and figure more out, but its nice to have something to go back to.

----------


## alansidman

Quakefire:  Here is a good starting point tutorial for Access

http://www.accessmvp.com/strive4peace/

and this one helps to understand table normalization

http://sbuweb.tcu.edu/bjones/20263/A...sDB_Design.pdf

Once you understand both of these, the next steps in development will make sense and ease the pain of the learning curve.

----------


## noboffinme

No problem, it really is worth having knowledge of Access as there is a lot it can do for you.

You will then understand the logic behind all relational databases.

Happy learning !!   :Smilie:

----------


## isi

Hi noboffinme;
I've seen your database and is great, but I have some other different related problems, because I must manage the stocks for a medical practice:
1. I have more than 2 expiration dates ( different lots)
2. I have not only medication but also materials and disinfectants, which I have in 3 different tables
3. For the database to be completed I try to integrate the registry (where I record consultations and treatments - treatments that should be substracted from inventory-)
For this I have a tables for: patients (with patients details), registry (with patientID, date, recommendations, etc), Diagnostic ( RegistryID, Diagnostic (1 patient with multiple diagnostics)), treatment (RegistryID, medID, quantity, MaterialID, Mat.quantity), meds and materials, and for inventory an in and an out table, an detail for the both of them with (MedID, quantity, expDate.
the problem is that it dosen-t work. 
How do I link the 2 parts, the registry linked with the treatment and the inventory. For me, at this moment is to complicated, so at least I decide to break the 2, one database with patient related things and one with the inventory.
The patient part it works .. so is done, but the other part I can't figure.
I need to track inventory fore multiple lots with different expiration dates, to be able to add, and subtract, and a system that warns me when something is going to expire.

----------


## alansidman

@Isi

Unfortunately _your post does not comply with Rule 2 of our Forum_ RULES. *Do not post a question in the thread of another member -- start your own thread.* 

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread. 

Old threads are often only monitored by the original participants.  New threads not only open you up to all possible participants again, they typically get faster response, too.

----------

