[stephenb52]

Hello!

I work for a small pharmacy chain that services long-term care homes. One of the things that we do is provide each home with an emergency kit that houses many drugs, should something drastic happen and the drug need right away. We have an Access spreadsheet that has a record of every time a drug is pulled from the kit, as well as how much was used. What we'd like to do is use Access (or Excel, for that matter) to analyze what drugs are used the most (and the least) and how much is usually used each dosage.

My idea is to get a query to look up a drug - but then I need it to display the number of times it occurs (the number of times it's been used), not list them. It'd be great to be able to get it to do some calculations with the dosage numbers as well - mean, median, mode. My ideal final product would be a table or report with all the drugs on the left, then the different calculations in columns.

So, first of all, is this possible? Would I be better off in Excel or Access? How would I accomplish this? Any help would be greatly appreciated.

[slurpee55]

First, welcome to the Forum!
It could be handled in either program, although Excel is better for generating the calculations (with VBA Access can do them as well, but....)
I would go for Excel. - be sure you have the analysis Tool-Pak enabled (under Tools, Add-Ins).
If you could post a file with some data (it can be dummy data, just make it somewhat realistic) and also a file showing what outcome you want we could help you more easily.

[OBP]

slurpee, I am sorry to disagree with you but this kind of analysis is perfect for Access Queries, no VBA required.
Using Excel would be a complete waste of time and effort, unless you need to do some very advanced statistics like linear regression on "F" testing.

[stephenb52]

I've attached a fraction of the data that I'm working with as well as a (very) small possibility for what I'd like to have as my product. Any and all suggestions are very welcome.

[DoubleHelix]

I know this is a technology forum, but aren't there some regulation and privacy issues here? I would think the FDA wants a better accounting of medication usage than an Excel spreadsheet. And HIPAA rules would affect the storage of data if any patient information is used.

[OBP]

stephenb, the attached database provides some queries that do simple statistics on the Sample data that you provided. It does not do Median, (why you would want to know the middle value is beyond me ). but it could be provided with very simple VBA.

[slurpee55]

Very nice, OBP...as for the median, I agree with you - I ran more complex stats on a few of the items and it is all over the place, with some of the items having a very large standard deviation as a result. For instance:

AMOXICILLIN 500MG
Mean 2.818181818
Standard Error 1.077186418
Median 1
Mode 1
Standard Deviation 3.572623177
Sample Variance 12.76363636
Kurtosis 1.908131833
Skewness 1.870029765
Range 9
Minimum 1
Maximum 10
Sum 31
Count 11
Largest(1) 10
Smallest(1) 1
Confidence Level(95.0%) 2.400120898
Your average of 2.82 (rounded) is the only item worth paying attention to from a statistical standpoint.
I suppose it might be useful to know what the most common dosage was....

[stephenb52]

Well, I was just kind of throwing out calculations. I thought that some instances might skew the averages a little bit, but in the real database (and even the sample, for that matter) there's enough data to remove most bias.

Slurpee and OBP, thank you so much for your help thus far. Those queries are nearly exactly what I was looking for, particularly "EDK Monthly Total Usage". One thing I'd like in that one, though, is the "CountOfID" column that's in the "EDK Total Usage by Wing" query. How could that be done? I'm trying to get a deeper knowledge of Access, but these crosstab queries are a little beyond me right now. I don't need a lot of detail, but could you tell me the basic gist of how you're writing these queries to get this information? Thanks again, guys.

Also, in response to Doublehelix, I'm almost entirely sure there's nothing wrong with displaying this information. There's no personally identifying information included - it's just some statistics on general drug usage. However, now that it's been brought to my attention, I think I'd rest a little easier if you wouldn't mind clearing out any "Wing" information, OBP. I have downloaded your queries, so you won't be any less appreciated if you delete that first attachment. Sorry to make you do more work when you've already helped out so much.

[OBP]

stephen, the Crosstab Queries are very easy to produce, there is a "Crosstab Query Wizard" that does it for you in the main menu "New" dropdown.
The other queries just use the "Totals" on the main menu when the Query is in Design mode.

You can use the ID instead of the "Amount" in the Crosstab, but not both, Crosstabs only work with one value.

[OBP]

Do you need any more assistance?

[stephenb52]

I've got a good enough start for now. I may ask for some additional help in a few days. Thank you very much.