Access db for CATI surveys

[annsolomon]

Our Marketing & Research dept does customer satisfaction surveys using a CATI system. Data is exported to csv files and I import to an Access table. The problem is that our clients frequently change question numbers on their surveys or add/delete questions from survey to survey. We're currently using a flatfile db format with lots and lots of tables & queries that have to be constantly changed. Some formatting is performed on certain data but, as I mentioned, that data may be Q1 (question1) in one survey and Q3 in another survey. Of course, the import spec has to be updated too.

Imagine my pain! Is anyone currently doing something like this more efficiently? I thought maybe I could open a form that prompts the user for the question numbers but I can't quite get past the idea stage. Can somebody give my brain a jump start? Ann

 

[beetee]

Yow, you have a real problem here.

Would it be possible for your marketing personel to provide some kind of survey ID in the data from the CSV file? Or perhaps use the survey ID in the name of the CSV file? Or, better yet, name the file <DestTable>.csv, where DestTable is the name of the table which will receive the data. Of course, you may not be privy to the changes until you receive the data.

If some nameing convention is used, you could write a little VB code to select an import specification based on the name of the survey, or by reading the first line of the survey, determing the survey type, and using the associated import spec.

A second alternative is to write VB code that imports the CSV data. The VB code would parse each CSV line into fields, and, based on the survey type, write the data out to the correct table. The you wouldn't need to continually modify import specs, but you would need to maintain some kind of table that maps CSV import file names to destination table names (unless of course the table name is included in the file).

Perhaps this will get you going in a (hopefully correct) direction.

P.S. the really awesome answer would be to create a total Survey application that the users must use so that the app could keep track of changes made to surveys, generate new surveys from existing surveys, incorporate generic answer processing methods so that all surveys could utilize a common results engine.

 

[annsolomon]

beetee, thanks for your comments. (My email must not be working 'cause I didn't get notified of your post.)

If I could expect a finite number of csv layouts, I could use your suggestions. Unfortunately, the combinations seem limitless since we have a good number of clients that each change their specs pretty frequently.

Your ideas got me thinking from a different angle and it now occurs to me that I'm not positive our survey output must be via an Excel spreadsheet. If I find I have some latitute there, I might be able to take advantage of the Access report's sorting and grouping feature which would save me a great deal of time on the tail end.

I'd still have the problem of the changing import specs though. For example, one survey might report on Q3 (&quot;How satisfied were you with the food in the hospital?&quot by hospital and department. The next survey for that customer might report on the same question but this time it's Q7 and they don't care about the department, just the hospital. Ugh!

Any other ideas? Ann

 

[beetee]

Since the CSV layouts are so varied, another suggestion would be to associate some sort of data dictionary with the CSV files. You could then write a generic data importation engine that takes data dictionary input and a CSV file, creating a table with the necessary fields and importing. I have some generic CSV import code if you're interested (uncommented, but tested). The code let's you specify a set of fields (currently in code) and imports data to a table. Additional code to create the table would be required. Also, it handles 'lookup' fields, where the value in the destination table is an ID in a lookup field.

Another question would be: is there some way to get data dictionary information from your CATI system? (I have no idea what a CATI system is).

The last paragraph sounds more like a change in report specs, not import specs, unless you're refering to missing data. Since I don't know much about reports, I can't be much help; other than to suggest that maybe you can make template reports and go from there, which is probably what you already do.

 

[annsolomon]

I've never worked with data dictionaries before so I'm totally lost. I'll get myself educated a bit then, if it looks like a viable alternative for our use, I'd gladly accept your offer of sharing. I'll let you know, okay?

By the way, CATI is a Computer Aided Telephone Interviewing system. It allows the administrator to design survey forms for data entry and exports the data for further processing with some other software app. Additionally, it automatically phones the interviewees and establishes callbacks as necessary. If I knew how to incorporate the telephoning part, I might have created my own survey db.

Thanks a lot for your feedback and offer of further help. Ann

 

[beetee]

I guess I meant the term data dictionary kinda generically.

The way I meant it was to associate a table structure (e.g. field definitions) with CSV files. That way, you treat a data to be imported as self-defining.

A data dictionary can also include data types; so if you find that UserName is always text, 32 characters, you can define a UserName data type and then use that type in table definitions.

 

[annsolomon]

Thanks for clarifying. I probably would have driven myself nuts trying to find out about data dictionaries on the Net. Ann