Multiple responses, new responses, in Access?
October 18, 2010 5:59 AM   Subscribe

MS ACCESS question (or maybe not?): I need to enter data from hundreds of marketing-type surveys. Most of the questions are free-form, but lots of people give the same answers. How can I make this easy for myself?

I'm thinking I should create an Access database and make a form. My problem is that for each question, I want to BOTH (a) be able to choose multiple existing responses (both "it's too big" and "i like the color"), AND (b) add new responses, which will then become chooseable from a dropdown.

Is Access the right tool? It's very important to me that data entry be keyboard-based, rather than trying to click checkboxes etc. with the mouse.

Is there a way to do the above in Access?
posted by dmd to Computers & Internet (7 answers total)
Response by poster: I'm seeing that I can create a lookup wizard field that can accept multiple values, and that I can do 'edit list values'.

The trouble is that in the form, there doesn't appear to be a way to enter values by keyboard, ONLY by mouse, which is extremely slow and cumbersome. Am I missing something?
posted by dmd at 6:05 AM on October 18, 2010

Response by poster: To be more specific... I don't care if I do this in Access or not, but I need a way to enter responses to questions such that
(1) once I've entered a response once, I can enter it again very easily
(2) I can enter multiple responses per question
(3) I can later get accurate counts of numbers of responses (this last one seems hard when things like Access just lump multi-select answers into a single comma-separated field).
posted by dmd at 8:23 AM on October 18, 2010

You can do this pretty easily with google forms, if you choose checkbox as your answer option.
posted by empath at 8:47 AM on October 18, 2010

there doesn't appear to be a way to enter values by keyboard

You can use tab to move around and Alt-down arrow to open up a combobox (dropdown)

I would look hard at doing this in a spreadsheet if there is a lot of copy/pasting and not much field validation. Typing into new cells will bring up previous entries, so you'd get something like the dropdown effect with that.

If you go into Access VBA code you could get a multiselect list box to do what you want, but it's probably more trouble than it is worth.
posted by okbye at 8:48 AM on October 18, 2010

Response by poster: Google Forms doesn't do (b) which will then become chooseable from a dropdown.

Once I've entered a new value for something, it needs to be chooseable for that item from then on.
posted by dmd at 8:48 AM on October 18, 2010

Response by poster: okbye: The problem I have with access is the above '(3)' thing - Access stores multi-select responses as comma-separated, and I can't figure out any way to then get a report that says how many people chose each item.

If person 1 chose 'A', person 2 chose 'B', and person 3 chose 'A' and 'B', I want to know that 2 people chose A and 2 people chose B. Access only tells me that 1 person chose A, 1 person chose B, and one person chose both.
posted by dmd at 8:51 AM on October 18, 2010

Yeah, you'd need to write a little code to do that. You'd need to know that you are looking for a string = "B" and then you could use the instr() function to see if the cell contains the string "B". Or in a query use wildcards like "* B *". You might want come up with a standard delimiter between the responses and then you could parse them out based on the delimiter. But it's code.

In the spreadsheet you could have a few columns that represent one response and combine them into a single pseudo column for reporting. Response 1 = column X & column Y & column Z.
posted by okbye at 9:04 AM on October 18, 2010

« Older Recovering data from Time Machine volume, without...   |   "Wah-Kom-Sin-Teek" is Navajo for "Starving Artist" Newer »
This thread is closed to new comments.