Excel or Access for Inventory control

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

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.

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

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.

Thread moved to access at OP request

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

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

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.

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 !!

@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.