INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

(OP)
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?

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

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?

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

(OP)
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

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

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.

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

(OP)
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.

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

(OP)
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?

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

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.pc2

Have fun.

---- Andy

There is a great need for a sarcasm font.

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

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.

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

(OP)
Great! Thanks

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

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.

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

(OP)
Very nice! Thank you.

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

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close