By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,995 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

Survey databases and distribution to interviewers

P: n/a
Hi,
sorry for the vague subject. I am working for a company that finds
respondents for surveys and then sends the respondent list and the
survey to another company to perform the actual surveys. In the past,
they have used Excel for this and, as you can imagine, the results are
a nightmare to work with because the resulting data is inconsistent and
denormalized.

I'm looking at Duane Hookum's At Your Survey and found his posts on how
to modify the database to include a "participant" and add demographic
and other data to that. At present, I'm considering turning the
database into an MDE and distributing it so that the interviewers will
be forced to enter normalized data (well for the most part). At least
then I can use other methods to figure out how to deal with
multi-valued attributes.

Right now the problems are many with the unworkable spreadsheet system
they've been using for 20+ years. (And I've been asked to clean this
up...) Sounds like i could apply security and deliver a database that
let the interviewers/callers add Survey records only (and responses).
Or is there a better way of going about setting this up and
distributing the database so that I can analyze the data relatively
painlessly? My thought is that I could merge this data into a bigger
master database and then have all the surveys and respondents in one
large DB which would be easier to maintain.

Right now the company has lots of spreadsheets with messy interview
results. The good news is that the boss is well aware that fixing the
old data will take time and probably a fair amount of manual labor.

Currently, it seems that he collects a fair amount of information about
the respondents and then has a separate logical section containing
questions (columns) and responses (rows).

Any thoughts on how best to proceed?

Again, apologies for the wandering somewhat vague post. If you have
questions, feel free to ask!

Thanks for the help!

Pieter

Jan 13 '07 #1
Share this Question
Share on Google+
12 Replies


P: n/a
On 13 Jan 2007 14:12:30 -0800, pi********@hotmail.com wrote:

Excel *can* be a vehicle for data collection, but you need to know
some advanced features and be able to lock the grid except for where
you'll allow data entry. You may also have a "add new row" button as
the only way to do that. We have successfully done this for several
hundred users in a few hundred hotels.

A web interface for data entry comes to mind. You can control the UI
so much better, and it automatically saves in the central db.

Mission-critical data does not belong in Access, with the cost of SQL
Server as low as it is. A single database would be my preference.
Whether that is feasible depends on the details. We worked on a
Marketing Survey where we are storing all data for more than 50
surveys, and those surveys are quite different from one another. It
can be done, and can be very powerful. You should see the correlation
reports we generate in PowerPoint from data between two studies.

-Tom.
>Hi,
sorry for the vague subject. I am working for a company that finds
respondents for surveys and then sends the respondent list and the
survey to another company to perform the actual surveys. In the past,
they have used Excel for this and, as you can imagine, the results are
a nightmare to work with because the resulting data is inconsistent and
denormalized.

I'm looking at Duane Hookum's At Your Survey and found his posts on how
to modify the database to include a "participant" and add demographic
and other data to that. At present, I'm considering turning the
database into an MDE and distributing it so that the interviewers will
be forced to enter normalized data (well for the most part). At least
then I can use other methods to figure out how to deal with
multi-valued attributes.

Right now the problems are many with the unworkable spreadsheet system
they've been using for 20+ years. (And I've been asked to clean this
up...) Sounds like i could apply security and deliver a database that
let the interviewers/callers add Survey records only (and responses).
Or is there a better way of going about setting this up and
distributing the database so that I can analyze the data relatively
painlessly? My thought is that I could merge this data into a bigger
master database and then have all the surveys and respondents in one
large DB which would be easier to maintain.

Right now the company has lots of spreadsheets with messy interview
results. The good news is that the boss is well aware that fixing the
old data will take time and probably a fair amount of manual labor.

Currently, it seems that he collects a fair amount of information about
the respondents and then has a separate logical section containing
questions (columns) and responses (rows).

Any thoughts on how best to proceed?

Again, apologies for the wandering somewhat vague post. If you have
questions, feel free to ask!

Thanks for the help!

Pieter
Jan 14 '07 #2

P: n/a
I would rather go directly to SQL Server if I can. Maybe the next
question is what's the best tool for normalization? Currently, I'm
planning on querying the Excel files and trying to figure out how to
speed up the cleaning process. Was thinking of inserting a column
between the Respondent data and the Responses data, so that I can find
it and get all the columns on each side in code. Then maybe this will
be less painful....

I guess this is where I have to actually install my demo copy of SQL
Server 2005 and start playing. (Well, as soon as I get enough surveys
to see what I'm really dealing with).

Time to splurge on more books, I think... and get real familiar with
BOL...

Jan 14 '07 #3

P: n/a

Tom van Stiphout wrote:
A web interface for data entry comes to mind. You can control the UI
so much better, and it automatically saves in the central db.
Tom,
what would be my best option if I wanted to create a web interface?
Use ASP.NET or something similar? Anybody have any good reference
material for that? Thanks!

Pieter

Jan 16 '07 #4

P: n/a

pi********@hotmail.com wrote:
Tom van Stiphout wrote:
A web interface for data entry comes to mind. You can control the UI
so much better, and it automatically saves in the central db.

Tom,
what would be my best option if I wanted to create a web interface?
Use ASP.NET or something similar? Anybody have any good reference
material for that? Thanks!

Pieter
material for that? Thanks!

Pieter
Are these surveys done real time via an interview process, i.e., via
telephone? If so, I would emphatically not use a web based approach
for this. In this situation you'd want the leanest, fastest means of
collecting the data and this would be a simple Access (Jet) database,
multiple copies of which could be amalgamated at the end of the day
into a central database of your choice.

Bruce

Jan 16 '07 #5

P: n/a

Bruce wrote:
pi********@hotmail.com wrote:
Tom van Stiphout wrote:
A web interface for data entry comes to mind. You can control the UI
so much better, and it automatically saves in the central db.
>
Tom,
what would be my best option if I wanted to create a web interface?
Use ASP.NET or something similar? Anybody have any good reference
material for that? Thanks!

Pieter
material for that? Thanks!

Pieter

Are these surveys done real time via an interview process, i.e., via
telephone? If so, I would emphatically not use a web based approach
for this. In this situation you'd want the leanest, fastest means of
collecting the data and this would be a simple Access (Jet) database,
multiple copies of which could be amalgamated at the end of the day
into a central database of your choice.

Bruce
Bruce,
yes, they're phone interviews, so it's all real time. The problem at
the moment is a total lack of control over what and how the
interviewers enter the data. So analyzing it is a nightmare or just
plain impossible. Granted that the company that hired me to fix the
mess only collects the data, but an analyzable result would be much
better. the other thing is that my boss doesn't want anyone to be able
to "steal" his data. Not that someone can't double-enter data or
whatever... I guess the viability of having webforms would depend on
connectivity speeds on both ends and the robustness of the server...

Jan 16 '07 #6

P: n/a
Tom
Have you looked at something like
http://www.surveymonkey.com/home.asp?Rnd=0.4797937 (and there are
others, this just happens to be one I remember) to collect the data?

might be easier than recreating the wheel (but certainly not more
entertaining!)

Good luck.
pi********@hotmail.com wrote:
Hi,
sorry for the vague subject. I am working for a company that finds
respondents for surveys and then sends the respondent list and the
survey to another company to perform the actual surveys. In the past,
they have used Excel for this and, as you can imagine, the results are
a nightmare to work with because the resulting data is inconsistent and
denormalized.

I'm looking at Duane Hookum's At Your Survey and found his posts on how
to modify the database to include a "participant" and add demographic
and other data to that. At present, I'm considering turning the
database into an MDE and distributing it so that the interviewers will
be forced to enter normalized data (well for the most part). At least
then I can use other methods to figure out how to deal with
multi-valued attributes.

Right now the problems are many with the unworkable spreadsheet system
they've been using for 20+ years. (And I've been asked to clean this
up...) Sounds like i could apply security and deliver a database that
let the interviewers/callers add Survey records only (and responses).
Or is there a better way of going about setting this up and
distributing the database so that I can analyze the data relatively
painlessly? My thought is that I could merge this data into a bigger
master database and then have all the surveys and respondents in one
large DB which would be easier to maintain.

Right now the company has lots of spreadsheets with messy interview
results. The good news is that the boss is well aware that fixing the
old data will take time and probably a fair amount of manual labor.

Currently, it seems that he collects a fair amount of information about
the respondents and then has a separate logical section containing
questions (columns) and responses (rows).

Any thoughts on how best to proceed?

Again, apologies for the wandering somewhat vague post. If you have
questions, feel free to ask!

Thanks for the help!

Pieter
Jan 16 '07 #7

P: n/a

pi********@hotmail.com wrote:
Bruce wrote:
pi********@hotmail.com wrote:
Tom van Stiphout wrote:
>
A web interface for data entry comes to mind. You can control the UI
so much better, and it automatically saves in the central db.

>
Tom,
what would be my best option if I wanted to create a web interface?
Use ASP.NET or something similar? Anybody have any good reference
material for that? Thanks!
>
Pieter
material for that? Thanks!
>
Pieter
Are these surveys done real time via an interview process, i.e., via
telephone? If so, I would emphatically not use a web based approach
for this. In this situation you'd want the leanest, fastest means of
collecting the data and this would be a simple Access (Jet) database,
multiple copies of which could be amalgamated at the end of the day
into a central database of your choice.

Bruce

Bruce,
yes, they're phone interviews, so it's all real time. The problem at
the moment is a total lack of control over what and how the
interviewers enter the data. So analyzing it is a nightmare or just
plain impossible. Granted that the company that hired me to fix the
mess only collects the data, but an analyzable result would be much
better. the other thing is that my boss doesn't want anyone to be able
to "steal" his data. Not that someone can't double-enter data or
whatever... I guess the viability of having webforms would depend on
connectivity speeds on both ends and the robustness of the server...
I understand and agree that the current spreadsheet method is a poor
one. I also feel that the web interface is a poor idea if real time
data entry is important. As soon as you have interviewers having to
wait for their screens to update with the next survey question, you're
going to have respondents simply hanging up the phone or saying to the
interviewer, "look, I have to be somewhere in ten minutes". The end
result will be the same...fewer satisfactorily completed surveys.
You're dead-on with your assessment that connectivity speeds on both
ends are important. Unfortunately these speeds are dependent on all
the things that can interfere with internet traffic between both ends.
I really feel your best solution will be coming up with a well
normalized relational model that accommodates the data previously
collected via spreadsheets and implementing that model in a simple
Access (Jet) database. This database need not even be split into
separate front and back ends. It can be encrypted and password
protected to prevent 'stealing' of the data. Each interviewer would
receive their own copy of the database. At the end of the day they can
email or ftp a copy to someone who will extract the data into a
centralized database. You could even write a service on a server to
automatically check for newly ftp'd files every so often and
automatically import their contents into a centralized database.
There's a lot you can do with Access alone. Having worked with both
Access and SQL Server back ends, there's not a lot of instances where I
would recommend a pure Access solution, but this is a situation that
would fit Access well.

Bruce

Jan 16 '07 #8

P: n/a

Bruce wrote:
pi********@hotmail.com wrote:
Bruce wrote:
pi********@hotmail.com wrote:
Tom van Stiphout wrote:

A web interface for data entry comes to mind. You can control the UI
so much better, and it automatically saves in the central db.
>

Tom,
what would be my best option if I wanted to create a web interface?
Use ASP.NET or something similar? Anybody have any good reference
material for that? Thanks!

Pieter
>
material for that? Thanks!

Pieter
>
Are these surveys done real time via an interview process, i.e., via
telephone? If so, I would emphatically not use a web based approach
for this. In this situation you'd want the leanest, fastest means of
collecting the data and this would be a simple Access (Jet) database,
multiple copies of which could be amalgamated at the end of the day
into a central database of your choice.
>
Bruce
Bruce,
yes, they're phone interviews, so it's all real time. The problem at
the moment is a total lack of control over what and how the
interviewers enter the data. So analyzing it is a nightmare or just
plain impossible. Granted that the company that hired me to fix the
mess only collects the data, but an analyzable result would be much
better. the other thing is that my boss doesn't want anyone to be able
to "steal" his data. Not that someone can't double-enter data or
whatever... I guess the viability of having webforms would depend on
connectivity speeds on both ends and the robustness of the server...

I understand and agree that the current spreadsheet method is a poor
one. I also feel that the web interface is a poor idea if real time
data entry is important. As soon as you have interviewers having to
wait for their screens to update with the next survey question, you're
going to have respondents simply hanging up the phone or saying to the
interviewer, "look, I have to be somewhere in ten minutes". The end
result will be the same...fewer satisfactorily completed surveys.
You're dead-on with your assessment that connectivity speeds on both
ends are important. Unfortunately these speeds are dependent on all
the things that can interfere with internet traffic between both ends.
I really feel your best solution will be coming up with a well
normalized relational model that accommodates the data previously
collected via spreadsheets and implementing that model in a simple
Access (Jet) database. This database need not even be split into
separate front and back ends. It can be encrypted and password
protected to prevent 'stealing' of the data. Each interviewer would
receive their own copy of the database. At the end of the day they can
email or ftp a copy to someone who will extract the data into a
centralized database. You could even write a service on a server to
automatically check for newly ftp'd files every so often and
automatically import their contents into a centralized database.
There's a lot you can do with Access alone. Having worked with both
Access and SQL Server back ends, there's not a lot of instances where I
would recommend a pure Access solution, but this is a situation that
would fit Access well.

Bruce
Okay. that's food for thought. I'm just exploring all the options
before I do anything. Right now my boss is looking at a custom SQL
Server DB that does this. If it has a web UI component, that could be
handy. If not... we'll see. But ANYTHING has to be better than Excel!
Anything!!!

Jan 17 '07 #9

P: n/a
On 16 Jan 2007 10:52:00 -0800, pi********@hotmail.com wrote:

ASP.NET is a BIG topic. Don't expect to pick it up in a few weeks.
But it is in my opinion the best tool to build dynamic websites.

-Tom.
>
Tom van Stiphout wrote:
>A web interface for data entry comes to mind. You can control the UI
so much better, and it automatically saves in the central db.

Tom,
what would be my best option if I wanted to create a web interface?
Use ASP.NET or something similar? Anybody have any good reference
material for that? Thanks!

Pieter
Jan 17 '07 #10

P: n/a

Tom van Stiphout wrote:
On 16 Jan 2007 10:52:00 -0800, pi********@hotmail.com wrote:

ASP.NET is a BIG topic. Don't expect to pick it up in a few weeks.
But it is in my opinion the best tool to build dynamic websites.

-Tom.
That's what I was afraid of... Of course, this means that the people
filling in the surveys would need a stable connection, but with a
reasonably high-speed internet connection, that should be fairly
trivial.

On the subject of ASP.NET, I have ASP.NET Developer's Jumpstart, but
it's oooold. Is there an updated version? (Or a better resource?)

Thanks!
Pieter

Jan 17 '07 #11

P: n/a
On 17 Jan 2007 10:09:12 -0800, pi********@hotmail.com wrote:

People don't need a reliable connection for web applications. This is
because of the stateless nature of web apps: each page stands on its
own. You request the next page, it downloads to the browser, and the
server almost forgets about you (keeping just session info, but no db
connections or anything other statefull).

MSFT offers a free download of the light version of Visual Studio.
Perhaps it's called Express Edition, not sure.

-Tom.

>
Tom van Stiphout wrote:
>On 16 Jan 2007 10:52:00 -0800, pi********@hotmail.com wrote:

ASP.NET is a BIG topic. Don't expect to pick it up in a few weeks.
But it is in my opinion the best tool to build dynamic websites.

-Tom.

That's what I was afraid of... Of course, this means that the people
filling in the surveys would need a stable connection, but with a
reasonably high-speed internet connection, that should be fairly
trivial.

On the subject of ASP.NET, I have ASP.NET Developer's Jumpstart, but
it's oooold. Is there an updated version? (Or a better resource?)

Thanks!
Pieter
Jan 18 '07 #12

P: n/a

Tom van Stiphout wrote:
On 17 Jan 2007 10:09:12 -0800, pi********@hotmail.com wrote:

People don't need a reliable connection for web applications. This is
because of the stateless nature of web apps: each page stands on its
own. You request the next page, it downloads to the browser, and the
server almost forgets about you (keeping just session info, but no db
connections or anything other statefull).

MSFT offers a free download of the light version of Visual Studio.
Perhaps it's called Express Edition, not sure.

-Tom.
Well, the boss made his decision - turns out he has a peer-to-peer
network, so I couldn't really run a web UI very well. he bought a
commercial package, so I'll have to just map the spreadsheet field
names to the database table fields. I was thinking of doing something
to help this, but I don't think it's possible because the data is not
normalized (he has Likert scales but as separate columns, so I'd have
to use queries to normalize that)... so I guess it might be just as
easy to import after cleanup. Should be interesting... maybe!

Thanks for all the input!

Jan 18 '07 #13

This discussion thread is closed

Replies have been disabled for this discussion.