What's the best software for this task?

July 6, 2010 7:46 AM

What is the best piece of software to do this task? And if it's Access, where can I get a good basics guide for newbies?

Here is the task I'd like to do:

I have a list of people, and I'd like to filter them out based on two things: whether they have a certain characteristic and whether they've done something a certain number of times in a certain period of time.

Yes, I am being purposely vague. I'll use a fake illustration: Let's say I want to filter out people who have a certain hair color (all the blondes, for example, but sometimes it may be all the redheads or all the brunettes) AND everyone who has eaten at a restaurant 5 times or more in the last month (but sometimes in may be in the last 6 months or the last year).

This sounds like an Access task. The thing is, I know virtually nothing about Access aside from entering data and that you can do queries. Is this something I can do with a very simple query? And if this is an Access task, where can I get a good (hopefully free/cheap) guide to learning it? I'm not at all opposed to learning more about this software if it's worthwhile.

I'm decent with Excel (I don't know macros though), so if there's a way to do it with that, I'm all ears. (I think filtering and sorting is too messy for this, as the dates portion is going to start growing.)

I'm also decent with statistical software (SPSS, SAS), but this task may get passed on to someone who isn't, so a bonus if it can be done in basic Office software.

posted by unannihilated to Computers & Internet (17 answers total)

If that certain characteristic and other criteria are in a table in Excel, you can easily do what you need with vlookup. It looks at criteria you define, looks for that in a table, in a specific column you indicate, and returns only the lines that match that criteria.

I would avoid Access. That way lies madness.
posted by Cobalt at 8:01 AM on July 6, 2010 [1 favorite]

It would help to know what format this data is currently in. Spreadsheet? Database? plain text? MS Word document?
posted by namewithoutwords at 8:04 AM on July 6, 2010

Best answer: Hells yes, you can do this in Excel. Using Access in this case may be like bludgeoning a mosquito with an anvil. AutoFilter is your friend in situations like this - you can selectively display rows using pretty-specific criteria on a NUMBER of columns at once. HOWEVER - a few columns of fake data would REALLY help with any recommendations for formulas/etc. Use the blondes/restaurants example, and tell us what will be in Column A, B, C, etc.
posted by julthumbscrew at 8:13 AM on July 6, 2010

Any relational database (SQLite, for example) would allow you to run this query without any problems. This is probably the "best" software, but it doesn't seem like the software you have experience with...
posted by beerbajay at 8:24 AM on July 6, 2010

You could create tables & queries in access without creating a full fledged application. Access for dummies should be all you need.
posted by canoehead at 8:44 AM on July 6, 2010

Best answer: Yes, use Excel and use Auto-Filter like julthumbscrew says.

Say you have name in column A, hair color in B and number of visits in C. Turn on Auto-Filter, and use the drop down menu in B to select the color. Then use the drop down menu in C to select "Custom...". Now a dialog box will open that lets you select "Greater than or equal to" on the left and enter a value on the right.
posted by soelo at 8:47 AM on July 6, 2010

I think you only need to use Access if you will be doing this often, you have a large number of records, or if you are going to get fancy with your criteria (like everyone who is blonde or more than five visits). Excel will let you select two different hair colors and any range of numbers you'd like.
posted by soelo at 8:50 AM on July 6, 2010

Response by poster: Follow-up:

The records are currently in Excel. Also, there are about 6,000 records, and the generated lists are also in the thousands.

A quick example (sorry, I tried to post actual fake columns, but I don't know how to keep blank spaces):

Column A: Contact info
Column B: the word blonde if they are blonde; everyone else is a blank cell
Column C: the word brunette if they are brunette; everyone else is a blank cell
Column D: the word redhead if they are a redhead; everyone else is a blank cell
Column E: title of column is Restaurant Eating on 01/01/10; everyone who ate at a restaurant at this date has the word yes, everyone else is a blank cell
Column F: title of column is Restaurant Eating on 02/14/10; everyone who ate at a restaurant at this date has the word yes, everyone else is a blank cell

There are a handful of hair color columns and they won't change. There are about a dozen restaurant columns and they will continue to increase.
posted by unannihilated at 9:26 AM on July 6, 2010

Response by poster: Oh, also, I would be generating a list about once per month, and the criteria for the list would change each month.
posted by unannihilated at 9:28 AM on July 6, 2010

Best answer: To keep doing this in Excel, you'll need a column that counts how many times it says "yes" in the restaurant columns. =COUNTA(F2:Q2) should do it. You'll need to update that formula each time you add a new column. You can do that by replacing ":Q" with ":R" in the entire column that contains the CountA formula.

The filtering will still work on the hair color columns. When you have data where every row has one and only one value, it often makes more sense to put it all in one column.
posted by soelo at 9:36 AM on July 6, 2010

Best answer: Soleo's got it - you'll need a new COUNTA column to tally total number of times they've eaten there - if the columns will continue to increase, you might as well put the new column waaaaaay the hell over on the right (make it Column AZ or somethin', and make the formula COUNTA(F2:AY2) so that any additional columns will be incorporated into the data automatically).

Then, when you've done that, do a Ctrl+A, then Data/Filter/Autofilter. Click on the wee drop-down arrow on the top of your new COUNTA column, then select "Custom" (you can then enter a "greater than or equal to" value). THEN, use the wee drop-down arrows over the hair color columns to select them (or not, as the case may be).

If you want to get REALLY fancy, you can put a SUBTOTAL(3,AZ2:AZ65000) function somewhere in the sheet, away from the other data- it will automatically count how many rows have been selected by any given AutoFilter that you've applied. Tres impressive!
posted by julthumbscrew at 9:56 AM on July 6, 2010

Response by poster: Oops...I should clarify. Perhaps hair color was a bad example because in reality, each record MAY have more than one value for those descriptive values. Generally I'm only filtering out ONE of these things, but I MAY do more than one on a rare occassion. Rather than hair color, a better example would be health conditions. Some people may have none and some people may have several, but I'm generally only filtering out one. Is this a problem for the solution you've described?
posted by unannihilated at 10:05 AM on July 6, 2010

Response by poster: Ah, nevermind...I just opened up AutoFilter and started playing. It looks like this should work fine if filtering out more than one. Thanks!
posted by unannihilated at 10:21 AM on July 6, 2010

Well, in that case, you'd select the AutoFilter drop-down and choose "Non-Blanks" (if you wanted to catch anyone with ANY value listed in that column) OR "Custom" (you can choose up to several possible values on which to filter). So: entirely doable. Make sense?
posted by julthumbscrew at 10:21 AM on July 6, 2010

Response by poster: Well, the values are all in different columns. That's always true. For each column it's either blank or X. But sometimes I want to select people who have values in multiple columns. Like if whether someone has MS (MS or blank) is in column D and whether someone has depression (depression or blank) is in Column E, and I wanted to select everyone who has both MS and depression. If I just apply a filter and just tick off depression and MS in their respective columns under the filter, that should get me everyone who has both, correct?
posted by unannihilated at 11:15 AM on July 6, 2010

Best answer: Yup - this is *exactly* the scenario in which AutoFilter shines - "Show me everyone with MS and depression who's eaten turkey tetrazzini more than two times in the past month". It's a process of progressively narrowing a group - just don't forget to turn the old filters OFF when you start messing around with new criteria; otherwise, NO rows will eventually show up and it'll get confusing.
posted by julthumbscrew at 11:54 AM on July 6, 2010

Response by poster: Excellent! Thank you very much for your help!
posted by unannihilated at 12:20 PM on July 6, 2010