Creating a survey

[AlexisBV]

Hello all,




I'm a newbie at Access, so so please bear with me if there's something obvious I'm missing. I'm using Access 2010 on WinXP.


I'd like to send out a survey to students regarding a set of courses they've taken. For each course, I have a few questions that are to be answered on a scale of 1 to 5, and one or two questions that are free-text. The questions are always the same for all courses.


How do I go about setting up this email survay? I've tried a few different things but it's not really working out. Here's what I have:


A table ("Courses") with fields for course ID, and course name.
A table ("Trainees") with fields for Student ID, a student name, and email address
A table ("Survey") with fields for record ID, course name (lookup to Courses), one field for each question, and the student's email address (lookup from Trainees).


The way I though this should work is that the Survey table would have one entry per student per course. So if there are 30 courses and 20 students, I expect the table to end up with 30x20=600 records.


What I'd like Access to do is to create the email with the questions for each of the 30 courses to each student.


However, if I start from an empty Survey table, the "create email" tool only creates one set of questions (instead of 30, one for each course), and does not automatically fill out the course name or even the person's email. So I'm guessing I need to prefill the Survey table with all the possible combinations of courses and students (600 records), then have each student update (instead of add) each of their survey records (and only theirs). Is that correct? What's the best way to do that (not by hand I hope)?


If so, how do I get Access to email each student only the questions under his/her name, instead of all 600 records?


Also, how to create an email with the actual course name, instead of the lookup number?


As a side note, I'm pretty sure all the trainees have InfoPath, so that could be an option for the form choice in the wizard if it provides needed functionality.


Thanks in advance,


Alexis

[June7]

Same questions for each course?

Do you want the course/question info built into email body or attached to email as a PDF? The PDF is easiest.

How do you want traninees to respond?

This will require queries that join tables and apply a filter and use of VBA code to build recordsets, loop through recordset, send email to each address.

[AlexisBV]

Yes, same questions for each course.

I'd prefer all the info to be built in the body of the email. Attachments can be confusing and people will make mistakes. I sometimes get a feeling that just an email might be too much to ask...

As for the trainee's response, I'm looking at what you'd get with most web-based surveys. As I mentioned in the initial post, some questions require a simple numeric answer (1 to 5). For example, a question would be "I understood the content of the course", and the answer could be a number ranging from 1-completely disagree to 5-completely agree. For one or two questions, the answer is optional and in free text (respondents can reply with comments in their own words).

I'm realizing that Access' support to this kind of thing isn't as straightforward as I'd hoped. Though I'm willing to continue investigating, and become better at Access in the process

[June7]

You want user to make response within the email and return that to you? This functionality is in the Collect Data wizard (be aware it is removed from Access 2013). I don't know if it would be practical for your requirements.