none
Update MultiValue Field Based on Value in Another Field RRS feed

  • Question

  • I have two fields in a table. This table was imported from Excel. (I am converting it to Access because the Excel tables have become too large and difficult to maintain with multiple people accessing it on a network.) I am attempting to clean up the data.

    I have a WireSize field (multivalue) I added to the table. It is linked to a tblWireSize, where all the values are kept. The other field is a text field [Wire Size]. It contains wire size values like 397 ACSR, 397 AL #397 AL, #397 ACSR, 397 ASCR, etc.... These are all the same wire size. In my tblWireSize there is only one value - 397 ACSR. When entering data in the table, employees will have only one choice so the data will be consistent. This problem exists for all wire sizes.

    I would like to be able to select the appropriate value in my new WireSize field based on the values in the old [Wire Size] field for all of the existing records (2670 records) so that I don't have to manually select the correct value for each record.

    I have figured out how to write a query to select all the values I want in the [Wire Size] field, but I don't know how to then select a value in my new WireSize multivalue field for the same records. Any ideas will be appreciated. I am very new to Access and VBA, so sorry for my lack of knowledge. Thanks in advance for help or ideas.

    Wednesday, August 12, 2015 1:44 PM

Answers

  • If I understand correctly,

    you could run an append query to change all values

    IN("397 ACSR", "397 AL", "#397 AL", "#397 ACSR", "397 ASCR")

    to 397 ACSR.


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Wednesday, August 12, 2015 3:42 PM

All replies

  • Hi. It may just be me, but I am having a hard time picturing what you need to do. Can you post some screenshots? Thanks.
    Wednesday, August 12, 2015 2:32 PM
  • If I understand correctly,

    you could run an append query to change all values

    IN("397 ACSR", "397 AL", "#397 AL", "#397 ACSR", "397 ASCR")

    to 397 ACSR.


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Wednesday, August 12, 2015 3:42 PM