Designing a questionnaire to distribute by email 3

[NigelLloyd]

I am an intelligent layman, and a beginner at Access (2000). I would like to design an Access form as a questionnaire, so that:
1. I send it as an email attachment to respondents,
2. they can fill it in and email it back,
3. I add it as a new record to my database.

I haven't a clue how/where to begin. Problems I foresee:
- what if the respondent doesn't have Access 2000 (maybe has '97 or nothing at all)?
- will it be possible to make the questionnaire 'idiot proof' - I hate filling in people's application forms that go wrong?
- how do I keep a separation between the blank form which I send out, the single completed forms which are returned and the database of all the forms/records returned so far?
- how do I enter the returned form into the database?
- how do I give a unique reference (key?) to each record?

I'm sorry that I need help on what must be the most standard of Access applications. If you could point me towards a textbook or give me monosyllabic advice, I would be most grateful

 

[WP]

Nigel,

You are correct in assuming that the technical merits of designing the questionnaire with its underlying table is a simple task.

The same cannot be said about the logistics of distribution and collection of data.

Like you say not everyone will have Access 2k therefore you cannot distribute in this format. Therefore you cannot collect in this format.

You need to decide on a format that people will be able to read and, of course, write to. A good choice might be Excel - every man and his dog has this. Again, the questionnare design will be simple but to the layman the data collection will not be so easy. You would have to read the Excel sheets from ACCESS. An easy task for the experienced ACCESS guru but not the beginner.

Bill Paton
william.paton@ubsw.com

http://www.neptune400.co.uk/

Check out

http://www.deepturtle.ch

 

[Guest_imported]

Nigel and Bill,

I just came across your discussion by accident and it's got me thinking. I wonder if it would be possible to send a questionnaire out in Excel format (or maybe just text) through an email and then fill the response details automatically into an Access database when an email reply is received. Just to complicate matters, would it then be possible to reply (i.e. send another worksheet/text file) using information directly from a database? I'm thinking of handling standard customer queries in this manner. Maybe Access isn't the best for this sort of thing and there's a better way to do this?

Hope you guys can help.

Keng

 

[WP]

Keng,

Anything is possible, but to go to those lengths you would be better designing a Web page for your questionnaire with the Access db as the backend.

This is not an easy subject. It will take time to get an elegant solution.

When I worked on a similar project I used Access and Excel. This was a commissioning project (shipbuilding). Field results were received in Excel format and placed in a receiving folder on a shared network drive. The Access db then read the contents of the receiving folder and then read each Excel sheet extrapolating the data of interest and posting it to Access tables.


Bill Paton
william.paton@ubsw.com

http://www.neptune400.co.uk/

Check out

http://www.deepturtle.ch

 

[NigelLloyd]

Dear Bill, many thanks for your helpful comments.
"not everyone will have Access 2k therefore you cannot distribute in this format" - I think it's possible to send a 'run-time version' of the Access questionnaire (see faq181-105) if I had the Developer edition of Access 2k. Is it possible otherwise? (I find Access complex enough without going for a more sophisticated version!)

"a format that people will be able to read and write to. A good choice might be Excel" I was hoping to use check boxes and drop down lists to make the questionnaire simple, but I don't think Excel could have these.

"the data collection will not be so easy" Could I just import an Excel file (carefully designed to be compatible) into my access database?

I still feel that there must be a write-up somewhere of this application. Or is there a custom-built application? Do you know of anything?
Nigel

 

[WP]

Nigel,

E-mailing the install for Access 2k is a non starter. The install files will be over a magabyte.

You would be amazed at what you can do in Excel - check boxes, combo boxes etc.

Yep, to make data collection easier you could indeed carefully design your excel form to be easily imported to ACCESS.

There is no easy solution. If I were doing this I would have a web based questionnaire. You send people the URL. They go to your page and fill in the details. The details are written to a server-side db. The rest is history.....

I'm not an expert in web page design but this work would not be difficult. Bill Paton
william.paton@ubsw.com

http://www.neptune400.co.uk/

Check out

http://www.deepturtle.ch

 

[KengLG]

Bill,

Thanks for your advice. I suppose a web page is the way to go. Time to hit the books.

Regards,
Keng

 

[BoxHead]

Nigel,

Check out the function statements available in Excel.

Depending upon how many people you need to send the questionaire to, I think there could be a simple solution. It requires some repetitive keying on the front end but automates your data collection.

The basic plan would be to paste your returned questionaires into an Excel Workbook and have the data automatically transferred to a Summary Worksheet at the front of the Workbook. Link this Summary Worksheet to your Access database and you're done.

1.) Design your Database table in Access.
2.) Replicate your Table fields on the First Worksheet in an Excel workbook (Sheet1, Row1)
3.) On the second worksheet, compose your questionaire.


By example: Your table has 5 fields:
Name Date Answer1 Answer2 Score

Sheet1 in Excel has the same values in Cells A1, A2, A3, A4, & A5.

Your Questionaire (Sheet2) allows for entry of the data in the following cells:
Name >B2
Date >E2
Answer1 >G14
Answer2 >G26
Score >L36

You want this information to be in a standard table format before importing it into Access. Therefore, you want the data from Sheet2 (Questionaire) replicated on Sheet1 (Table):

Name >A2
Date >B2
Answer1 >C2
Answer2 >D2
Score >E2

On sheet1, cell A2 enter the function:
=IF(Sheet2!B2 <> &quot;&quot;, Sheet2!B2, &quot;&quot

Now, whatever name is entered in the Questionaire will be displayed on the Table. The &quot;IF&quot; function makes sure that there is a value entered in the Questionaire Name cell. Repeat this step in the other cells on Sheet1.

Name A2 = IF(Sheet2!B2 <>&quot;&quot;, Sheet2!B2, &quot;&quot
Date B2 = IF(Sheet2!E2 <> &quot;&quot;, Sheet2!E2, &quot;&quot
Answer1 C2 = IF(Sheet2!G14 <> &quot;&quot;, Sheet2!G14, &quot;&quot
Answer2 D2 = IF(Sheet2!G26 <> &quot;&quot;, Sheet2!G26, &quot;&quot
Score E2 = IF(Sheet2!L36 <> &quot;&quot;, Sheet2!L36, &quot;&quot

Now, to the right of Sheet2, insert 24 additional worksheets. If you'll have 25 people participating in the survey, you'll be able to Link your Access table to the workbook. If you'll have more participants, you'll want to do periodic imports.

On the Summary Worksheet (Sheet1) you have to repeat the function statements in Rows 3 through 26. The statements in Row3 have to be changed to reference Sheet3, Row4 references Sheet4 and so on.

When you receive your first questionaire back, paste it into Sheet2. Paste the next into Sheet3 and so on. The Summary worksheet will automatically be updated in a clean layout for importing or linking.

If you decide to try this route and would like some ways to reduce the key-entry on the summary worksheet, let me know.

 

[DougAtAvalon]

I have built this professionally using microsoft word and access if interested post here I'll get you started.

 

[NigelLloyd]

Doug,
Yes, I would appreciate the benefit of your experience; even more people have Word and can use it than have Excel.
Nigel (NigelL@CamProf.com)

 

[DougAtAvalon]

The basic code you need is:

**********************
Set vFile = YourWordFile
Set myWord = CreateObject(&quot;Word.Application&quot

AppActivate &quot;Microsoft Access&quot; 'Make sure access is shown I forget if you actually need this

VariableString = GetObject(vFile).FormFields(NameOfFormField).result

myWord.Quit
Set myWord = Nothing
****************

This is the basics.
There are many other factors like size of questionnaire field types, etc that take time to figure out on an individual basis.

NOTE: while debugging you may have to &quot;End Process &quot; (Ctrl -Alt-Del) of MSWord. The code will leave an instance of word running if you don't &quot;quit&quot; it.

-Doug
dr@avalonmall.com

 

[pontellt]

Suggest you invest in a relativly inexpensive email survey package. Check into SNAP by Mercator for example.