Computer/Technology problem for you gurus...

[dchogs]

ladies and gentlemen of ES, i need your help...

i've been tasked with a job that i don't know how to do, or even if it is possible. if anyone can point me in the right direction, i'd sure appreciate it. here's the scoop:

i need to create some kind of filter that will take current data and compare it to a series of data ranges to see if the current data "fits." it's along the lines of seeing if zipcodes fit in with areas of the country that we're targeting for new business.

so, can i do this? i have moderate computer skills, but no formal training or coding experience. what would someone have to know in order to get this job done?

thanks for your help!

dch

[herrmag]

Not really sure what it is exactly that you are trying to do (more specifics), but I assume that there will be some data comparison involved (i.e. compare data from one zip code to a standard set of data to see if it is a good match). There's a good tool that can compare files if they are in the same format that might be able to help you. http://www.prestosoft.com/ps.asp?page=edp_examdiff

[mjah]

Sounds like a reasonable task. A couple of options:

1) Write a program to do just that. It can be called at any time and read input from a file; do all the comparisons; and display or write output. Probably your best option if you have a huge number of comparisons to make, or if this will be running on a regular basis.

2) Spreadsheet. You can paste in the input, see what it's doing, and then copy/paste the output. This might be better if it's a one-off or two-off comparison.

There are other ways too, but these are the two I'd look into.

Can you tell us more about how/when/how often it will be run and how many ZIP codes it will take in?

[Corcaigh]

Describe the process you want to perform step-by-step if you were doing it manually with pen and paper.

Then, either yourself, or someone with reasonable MS Excel/MS Access knowledge could evaluate if you can automate the process adequately using those tools.

If not, it's something a program could be written to support, either in-house or externally.

[dchogs]

good questions... thanks.

essentially, we're entering 10k-15k zipcodes with demographic qualities that have been successful historically in terms of marketing and student retention. with that information, we want to "screen" new zipcodes against the historic data as a quick and dirty indicator for 1) future marketing efforts and 2) prospective students.

basically, if i'm thinking of doing a marketing push in york, pa (just happens to be my hometown), i'd want to check to see if 17402, 17403, etc were on the list of 15,000 "good" zipcodes without scrolling through them all manually.

[chipwhich]

good questions... thanks.

essentially, we're entering 10k-15k zipcodes with demographic qualities that have been successful historically in terms of marketing and student retention. with that information, we want to "screen" new zipcodes against the historic data as a quick and dirty indicator for 1) future marketing efforts and 2) prospective students.

basically, if i'm thinking of doing a marketing push in york, pa (just happens to be my hometown), i'd want to check to see if 17402, 17403, etc were on the list of 15,000 "good" zipcodes without scrolling through them all manually.

You need to start learning Microsoft Access. Thats the best option for someone with moderate skills and is good for what you want to do.

[Brad_Edwards_Fan]

good questions... thanks.

essentially, we're entering 10k-15k zipcodes with demographic qualities that have been successful historically in terms of marketing and student retention. with that information, we want to "screen" new zipcodes against the historic data as a quick and dirty indicator for 1) future marketing efforts and 2) prospective students.

basically, if i'm thinking of doing a marketing push in york, pa (just happens to be my hometown), i'd want to check to see if 17402, 17403, etc were on the list of 15,000 "good" zipcodes without scrolling through them all manually.

I will answer asuming you dont have anyone that could write a program in a program like SAS which would make this very easy.

If you have two lists that both have zip on them you oculd use a v-lookup in excel to essentially append the data from the performance list to the other list

[egtuna]

SQL:

Select * from <tablename or names> where

<zipcode field> in ('<zipcode1>', '<zipcode1>')

where

<your "good" zipcode field> = <good zipcode field value>

If your zips are returned they are "Good".

[DCSaints_fan]

good questions... thanks.

essentially, we're entering 10k-15k zipcodes with demographic qualities that have been successful historically in terms of marketing and student retention. with that information, we want to "screen" new zipcodes against the historic data as a quick and dirty indicator for 1) future marketing efforts and 2) prospective students.

basically, if i'm thinking of doing a marketing push in york, pa (just happens to be my hometown), i'd want to check to see if 17402, 17403, etc were on the list of 15,000 "good" zipcodes without scrolling through them all manually.

Thats not that big of a data set, Excel should be able to handle it. Then you merely write a formula to fill a column to restrict with good data. However a database oriented program like Access would make queries nicer, especially if you plan on any further expansion of the data you acquire.

[DCSaints_fan]

SQL:

Select * from <tablename or names> where

<zipcode field> in ('<zipcode1>', '<zipcode1>')

where

<your "good" zipcode field> = <good zipcode field value>

If your zips are returned they are "Good".

An SQL query would be ideal but there is the problem of an easy to use database engine that allows easy data entry and/or importation. MS Access is the only one I know of offhand. In general using an relational database and populating a table from something like a plain text file will require programming knowledge.

[Pwyl]

How often do you need to do these screenings and what file format is the data in?

[dchogs]

I will answer asuming you dont have anyone that could write a program in a program like SAS which would make this very easy.

If you have two lists that both have zip on them you oculd use a v-lookup in excel to essentially append the data from the performance list to the other list

i'm not sure if our IT guy or anyone else can write for SAS. i don't even know what SAS is.

i'll try the excel route.

also, i'll take a look at MS Access as well.

to answer other questions... we do plan on continuing to add "good" data to our database, and we'd be running these screenings about once a month.

the sql query looks good, but, assuming i'm reading it correctly, my boss wants it the other way around (not that he has to have it that way). he wants to enter 17402 and essentially get a thumbs up or a thumbs down.

keep the options/opinions coming... this is great help!

[DCSaints_fan]

If you don't have access to MS Access, you can try OpenOffice.org Base, which is similar although I've never used it. (I do use Calc and Write however, and they are acceptable for my needs) You can download it for free at http://openoffice.org

[dchogs]

i do have access and am checking it out now.

[mjah]

dchogs, check your PMs.