Parse a string into single fields

[wire_jp]

Hello,

I have linked a Google Sheets which was generated from Google Forms survey questionnaire to a Microsoft Access

The field [BINGO Sessions:Please select all areas which apply: -] contains the following option data results: -
1. Promoting the event among family and colleagues and encouraging participation.
2. Assisting with the sale of admission tickets and cards.
3. Sourcing donor sponsorship for prizes.
4. Helping to run the sessions.

How do I parse this data (line items 1 - 4) into separate fields and how to deal with situations where a respondent did not select an option(s) e.g. line 2 is missing or line item 2 and line item 4 are missing?

Thanks

[HansV]

The best way to handle this would be:

• Create a new table tblOption with two fields:
  
  OptionID: Number, Long Integer), Primary Key
  Option: Short Text, length 100.
  
  Add records like this:
  
  
  S2468.png
• Create another table tblResponse with (at least) two fields:
  
  SurveyID: A field that can be linked to the unique identifier of the questionnaire table. Its type depends on the type of that identifier.
  OptionID: Number, Long Integer. This field will link to the OptionID field in the table above.
• Create an append query based on the questionnaire table that appends to tblResponse, adding the unique identifier to SurveyID and and the value 1 to OptionID, with criteria for the [BINGO Sessions:Please select all areas which apply: -] field:
  
  Like "*1.*"
  
  Run this query.
• Change the value to be added to OptionID to 2 and the criteria to
  
  Like "*2.*"
  
  Run the query again.
• Repeat for 3 and 4.

[wire_jp]

Hi Hans, thank you for your help.

[wire_jp]

Hi Hans,

I followed the steps which you outlined below. However, the append query called "qryBingoAppendQuery" is not working properly. When I set a criteria
Like "*1.*"

no records are appended. However, if I remove the criteria, and I run the append query. I receive an error message:-

Code: Select all

database set fields to null due to a type conversion failure and it didn't add 0 records to the table due to key violations, 0 records due to lock violations and 0 records due to validation rule violations. 

I have attached a shared OneDrive link which contains the Microsoft Access Database and the linked Excel spreadsheet, for your review:-
https://1drv.ms/f/s!AtOdPG-IN8CYgcV-YYl ... Q?e=gdZQNA

I used the tables:
tblBingoOptions
tblBingoResponses
tblFormResponses (the linked Excel spreadsheet called)


Thanks

[HansV]

The values of the [BINGO Sessions:Please select all areas which apply: -] field are different from your first post.
The append query should have this SQL:

Code: Select all

INSERT INTO tblBingoResponses ( BingoSurveyID, BingoOptionID )
SELECT tblFormResponses.[BARP member number], tblBingoOptions.BingoOptionID
FROM tblFormResponses, tblBingoOptions
WHERE (((tblFormResponses.[BINGO Sessions_Please select all areas which apply: -]) Like "*" & [BingoOption] & "*"));

S2470.png

Result of running the query:

S2471.png

[wire_jp]

Hi Hans,

Thank you for your help. This is awesome!!