(HELP) Excel and Access for CATI Survey

[artronnie]

I got this quite complex problem considering my skill. I'm planning to develop CATI survey for our company which will involve using Excel and Access. Excel will be the front end while Access as the database storage. These two are readily available and we can't use any other software. At the beginning, I will have 5 interviewers doing this simultaneously. Later on, this number might be increased up to 10 (max). I’m sure that the Access and Excel configuration able to cope with this scale.

Below is how I planned things.

1. Interviewers will entry data to excel. He/she will have a split access database file (access front end).
2. Access will have two tables. First one is call database (where all respondents call date/time and status stored) and the next table is responses to the survey itself.
3. When my interviewer wants to call respondent, I need excel to check Access Call Database table record whether this respondent had been contacted before or not. If not (i.e., no record on the access call database), excel will display status that this is new respondent. If yes, excel will fill in prepared field (one respondent is allowed to be contacted 5 times) for this specific record.
4. After the interview, I need all recorded responses to be transferred to Access Survey Response table.

That’s all. The question, how do I accomplish the above task using ADO and VB? Any help would be greatly appreciated. Thanks in advance and sorry for the noobish questions.

 

[Norie]

Why not do the whole thing in Access?

You could do it with Excel and Access but as you seem to have worked out it might involve a fair amount of code, eg ADO.

Why do you seem to want to use Excel as the frontend?

It's not hard to set up a user friendly UI in Access, in fact it might be easier than doing it in Excel.

 

[artronnie]

Thanks for the reply. I need excel because the questions are dynamically generated. Hard coding the questions will need lots of form (instead of one if using excel). I can use excel formula and vba for that, quite easily. As of now, i'm not aware on how to do that in access. beside, my interviewers are familiar with excel.

I managed to do excel to access ado code for the survey responses part. But going nowhere on the call database part

 

[Norie]

Sorry, but that isn't quite right.

What makes you think you couldn't dynamically generate questions in Access and why would you need multiple forms?

How are you currently doing it in Excel?

I can think of a few ways of generating questions but how to do it exactly would really depend on what you actually mean by 'dynamic'.

I can understand in a way about the users being familiar with Excel but what part of Excel?

 

[artronnie]

Thanks again for the reply.

Sorry, but that isn't quite right.

What makes you think you couldn't dynamically generate questions in Access and why would you need multiple forms?

I have 4 groups of customers (i.e., priority banking customer, regular banking customer, personal loan customers, and card customer). Each groups will have 5 - 10 touch points (those who've contacted call center, those who've been contacted by relationship manager, etc). Thus, I need 20 - 40 set of questionnaires. I didn't mean to say that I think I couldn't do that in access. I'm not aware on how to do that in access and neither do the supervisor who will manage the project operation.

How are you currently doing it in Excel?

I setup questionnaire "form" which will be the interviewer interface and another sheet containing all the questions sets. Use lots of & operator to combine questions, insert customer name, insert touch point name, etc. Interviewer just need to select the group and touch points name and then they will get specific questions set targeted at that particular group.

I can think of a few ways of generating questions but how to do it exactly would really depend on what you actually mean by 'dynamic'.

Example: two worksheets, "Form" and "QNRE". One formula in Form D3 (after an IF selection of group and touch point via macro:

=QNRE!F5&QNRE!F6

There are certain condition where F5 and F6 will be changed in the future. etc., hope you get what I mean.

I can understand in a way about the users being familiar with Excel but what part of Excel?

Well, let me just say that all of the users never use access before

 

[Norie]

Sorry to mention it again but as far as I can see that could all be done in Access.

It might take a bit of work to initially set up but it might actually be worth it in the end.

Anyway if we go back to the original question, you say you're having trouble with the 'call database' part of the process?

What problems are you having? Transferring the data from Excel to Access?

That should be pretty straightforward depending on how you are recording the data in Excel.

Do you have a worksheet/worksheets were all the calls made are recorded?

Something simple with a column for date/time, interviewer, respondent/customer should cover that, and you could replicate that in a table in Access.

Obviously that's very simple but it would be straightforward to add other columns/fields, eg reason for call, was respondent in etc.

That's how I think I would store the data.

If it was then it would be quite straightforward for an interviewer to pull a 'history' of the calls made to a client/respondent.

At this point I'm starting to wonder if you already have a client/respondent table where there would be a record for a client even if they've not been called.

Does this make any sense? I'm kind of thinking off the top of my head, perhaps if you could post more about the specific problems you are having.

 

[artronnie]

Actually, I was thinking of doing this all at Access per your advise. What would be the cons against doing excel (FE) and access (BE)? Something like losing data?

 

[Norie]

Losing data would be a possibility but there could also be other issues.

Off the top of my head the first one I can think of is corruption of the database.

That could happen if you did the whole thing in Access though, and that's why it might be an idea to have an Access FE-BE.

In the FE would be the forms/reports/code etc and in the backend the tables etc with all data.

If the data was be stored centrally, perhaps on a server, then it would greatly help avoid corruption/loss of data as the most likely cause of any problems would be
in the frontend.

Mind you it might be an idea to start out with everything in one database, just for ease, then at one point split the database.

There's even a wizard to help you with that, though just like any wizard I wouldn't rely on it for the final product.

Actually the major advantage of using Access would probably be that it would just be easier.

Some of what you seem to want/need could be acheived using inbuilt Access functionality.