Update new Multivalue checkbox field with existing data from other fields

[ama]

I have a database table called "Client" with these relevant fields:

Client.ClientID
Client.Crime2
Client.Crime3
Client.Crime4
Client.Crime5
Client.Crime6
Client.AdditionalVictimizations

The Crime2-6 fields are Lookup ComboBox fields querying the "ListCrimes" table with these fields:



ListCrimes.Detail
ListCrimes.CrimeID
ListCrimes.Abbr [the field that displays in the Crime2-6 and AdditionalVictimization fields in the Client table]

The "AdditionalVictimizations" field is a "multivalue checkbox" field that also gets its data from ListCrimes.Detail using a Lookup ComboBox query. The AdditionalVictimizations field is currently without data. It was added to simplify new data entry where a staff person can enter multiple values from one field instead of five.

Going forward is easy, but I want to update or append the existing data in the Crime2-6 fields into the AdditionalVictimizations multivalue field, and then delete the Crime2-6 fields. Can anyone help me with the append or update query? I don't know SQL, but can copy and paste just fine if SQL is the only way to do it!

Thanks in advance....

[June7]

I NEVER use multi-value fields. They are a headache to deal with. Review https://support.office.com/en-us/art...624E1E323A#bm7

Simplest approach is to build a related table and populate Crime fields as records into that table. This is essentially what a multi-value field is - only Access hides the dependent table.

A UNION query can rearrange the Crime fields into a normalized structure and then use that query as source for INSERT action.

[HiTechCoach]

I have a database table called "Client" with these relevant fields:

Client.ClientID
Client.Crime2
Client.Crime3
Client.Crime4
Client.Crime5
Client.Crime6
Client.AdditionalVictimizations

The Crime2-6 fields are Lookup ComboBox fields querying the "ListCrimes" table with these fields:

ListCrimes.Detail
ListCrimes.CrimeID
ListCrimes.Abbr [the field that displays in the Crime2-6 and AdditionalVictimization fields in the Client table]

The "AdditionalVictimizations" field is a "multivalue checkbox" field that also gets its data from ListCrimes.Detail using a Lookup ComboBox query. The AdditionalVictimizations field is currently without data. It was added to simplify new data entry where a staff person can enter multiple values from one field instead of five.

Going forward is easy, but I want to update or append the existing data in the Crime2-6 fields into the AdditionalVictimizations multivalue field, and then delete the Crime2-6 fields. Can anyone help me with the append or update query? I don't know SQL, but can copy and paste just fine if SQL is the only way to do it!

Thanks in advance....

I agree with June7. I also I NEVER use multi-value fields. They were added to help with Web App hosted in Sharepoint.


Also in your design you have repeating field:

Client.Crime2
Client.Crime3
Client.Crime4
Client.Crime5
Client.Crime6

These really should all be in a sub/child table with one record per crime.

The is also true for the Client.AdditionalVictimizations as June7 has previously pointed out.

Making the leap from a spreadsheet design (single table) to a relational database (multiple related tables) is a huge leap. In the long run it is really worth it. By harnessing the power of a relational design you will be able to add Client.Crime7, Client.Crime8, not by having to edit the table and add fields and update many queries, forms and reports, but simply add additional records. No need to make any design changes.

[ama]

Thanks for the comments from both forum members. I've decided to abandon the idea of the multivalue checkbox. In looking more deeply at the database I inherited, I have noticed many fields that would be better organized into linked tables and have started the process.