Access UPDATE Query with Multi-valued Field

Question

Hello Community,

I am trying to import data from an Excel document into an Access database.  I imported the Excel document into a table.  Database is for retirement home residency application.  In the Excel document table, there is a single true/false column for each tower selection (6 in total), but in the database table I want to use I have a single lookup field for selecting the towers from a tower field.  

I would like to run a simple query that checks which of the excel document table columns are set to true, then updates the tower field in the other table to include that selection.  However that results in the error "An UPDATE or DELETE query cannot contain a multi-valued field."

Basically I just need to know if there is a quick way I can import the tower selections from the excel document table so that I don't have to go through each of the 958 records selecting which towers they have applied for.  Using true/false fields for each tower would mean altering all of my queries, forms, and reports; plus that would take up more space in the already crammed reports.

Thanks.

Answer 1

"INSERT INTO tblWaitingList (Tower.Value) SELECT TT1 FROM excelstuff WHERE ID = ApplicantID"

Does that look correct?

Seems to me it's attempting to select the boolean value from the tower field and insert it into the MVF.  Perhaps I didn't explain the situation well enough.  Thanks for your help though!

Answer 2

Hi. Did you try the SQL I posted earlier? It should work provided you use the correct field names.

Answer 3

Thanks for the response.  Where I'm stuck is in that demo you moved the values from one MVF to another, but I need to set the MVF values for each record based on a non-MVF.  

In the INSERT statement values where you have "('" & !NewMVF & "')" I think I need to manually input the values (tower id's) like this: "(1,2,3,4)." I tried it, and there weren't any errors but it didn't work.

It seems like the answer is probably there but I just lack the knowledge and expertise to extrapolate it.

Answer 4

Hi. You can use an INSERT statement to add the Towers to the MVF, but you'll have to do it for each record.

INSERT INTO TableName (FieldName.Value) SELECT Tower FROM OtherTable WHERE FKID=PKID

See if this demo can give you some ideas. Hope it helps...