How to extract the values of a multi-value field. 1

[FancyPrairie]

I'm in the process of converting Access tables to SQL tables. However, several fields within one of the tables allow multiple values. How do I extract the value(s) of a multi-value field?

 

[LaurieHamlin]

Do you mean that any record may have one of several values? Or does that field sometimes have multiple values? If the later, how are they separated?

 

[FancyPrairie]

On the Lookup tab on a field definition, one of the properties is defined as "allow multiple values". The "display control" property would be set to ListBox. On a form, the listbox control will display checkboxes from which the user can select multiple values. In datasheet view, the multiple items are separated by commas. However, when I view the field via an ado recordset, the value property has a field property but it only contains the first item. I need to know what all of the items selected are.

I think I have found a way to do it though. I exported the file to a SQL database. The items selected are now in a ntext field separated by semicolons. I can now create another table that will be normalized. For example:

Access Main table: ID=1; Stores=Walmart,Walgreens,Sears; Several other fields
New Access Main table: ID=1; Several other Fields
New normalized table: ID=1; Stores=Walmart
ID=1; Stores=Walgreens
ID=1; Stores=Sears

 

[MajP]

Normalizing a multi-valued field is extremely simple.

Assume I have a table
MyTable
ID
MV_Field

With data like

1 A,B,C
2 A,C
3 C,D

Select ID, MV_Field.value from MyTable

would produce
ID MV_Field.Value
1 A
1 B
1 C
2 A
2 C
3 C
3 D

This can be done from the QDE, just expand MV_Field.

 

[FancyPrairie]

Bummer! I just finished writing a function for each of the multi-value fields (about 12 of them) to do the conversion. Wasn't too bad though. Once one was done it was simply a copy and paste. However, your method would have been better and saved me time. Oh well.

 

[FancyPrairie]

MajP,

I had a user create the Access database to be used by people in her department. She did an ok job, but the way she set it up requires a bunch of changes at the beginning of each fiscal year. So I'm in the process of rewriting it for her. The users have been using her database for 3 or 4 years and so are use to selecting multiple values from a multi-value combobox with checkboxes. Since I had to move the tables to a SQL server and since SQL does not have a multi-value field, I had to create the tables described above. In order for the user to select 1 or more items I'm going to have to create a subform for each of the multi-value items (approx. 12). I'm not sure the users are going to be happy about that since selecting the items is much easier the way it was.

Is there a way that I can have a multi-value combobox with checkboxes with the scenario I now have?

 

[Andrzejek]

Don’t you just hate / love (depending on which side you are on) when somebody shows you an easy way to do stuff?
Once my co-worker worked hard for two days straight to accomplish a task. On third day I showed her my way – which took me about a minute of work. (I did not know she was working on it)

Procrastination has its good sides.


Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[MajP]

I have a demo that someone did to simulate the multi combo listbox. It is very slick. Wish I though of it. I will try to upload if I can find it.

 

[FancyPrairie]

Great! Thanks

 

[MajP]

Go to frm_Hold. In desingn view look at cbodefects and below it the subform fsubdefects. You will have to look at the code in cbodefects and on the subform.
Now you could simplify this code a little by just putting a boolean field in the defects table. Then you could populate the checkboxes. They actually in ADO take the defect table and append temporary boolean field using the SHAPE command. I am not familiar with that. However, the basic logic of populating/unpopulating the checkboxes and deleting/adding child records is what has to be done.

If I was going to do this for 12 combos, I would concentrate on writing good reuseable code that that once you build the subform you can reuse it anywhere by specifying the recordset for the subform.

 

[FancyPrairie]

Very nice! Thank you.

 

[MajP]

I will play with this a little and see if I can make this a generic class, that way it can be used on any form with only a little code change.