Hi,
I need to import data from an Excel spreadsheet that has a column with cells that have multiple values. The Excel spreadsheet was generated by Google Forms. The column with multiple values was populated by a question asking "What requirements does this class fulfill?" and the response is a series of check boxes. The user can check more than one box.
The entries in the Excel spreadsheet look like this:
Answer1, Answer2, Answer3 - If the user checked boxes for Answer1, Answer2, and Answer3
Answer2, Answer3 - If the user checked boxes for Answer2 and Answer3
etc
The corresponding fields in Access are currently setup as:
Data Type - Text
Display Control - Combo Box
Row Source Type - Value List
Row Source Type - "Answer1"; "Answer2"; "Answer3"
Allow Multiple Values - Yes
Limit to List - Yes
If I try to Import External Data (append), I get the error message:
"The contents of 1 records were deleted, and 0 records were lost due to key violations.
*If data was deleted, the data you pasted or imported doesn't match the field data types or the FieldSize property in the destination table.
*If records were lost, either the records you pasted contain primary key values that already exist in the destination table, or they violate referential integrity rules for a relationship defined between tables
If I import the Excel spreadsheet into a new table, and then change the Lookup for the column in question, I end up with combo box fields like this:
[] Answer1
[] Answer2
[] Answer3
[] Answer1, Answer2, Answer3
[] Answer2, Answer3
So it looks like the multi-value fields from Excel are just creating new possible values in the combo box instead of checking multiple boxes like it should, even if I have "Limit to List" set to "Yes" in the Lookup for that field.
I hope this all makes sense. Any help would be greatly appreciated. I am obviously a total newb at this, but I have not found the Access help files very helpful. Thank you!