Query operations for searching a text field containing multiple values.

[gwboolean]

I have a Visual Basic form that is using two tables from an ACCESS database. The form displays a single item/part/assembly data from an item table. Additionally, there is a datagridview that shows a list of vendors (from another table) that provide the item (see form below).



Each item record has a unique 9 digit ID number. e.g. 901003111. In the vendor table each vendor record has a text field that contains each of the item ID numbers of the items associated with that vendor record. The field might look like this:

901003111, 100234758, 111900231, ........,

Currently, the values in this field are comma delimited. The reason for the multiple values in the field is because a vendor might be a source for more than one item.

So what I am trying to do is to display an item, like in the form below. Then run a fill query that would search the Vendor records ID field for a value that matches the Validation ID # shown in the form and then display all of those Vendor records that contain that value in the datagridview.

Can anyone explain to me how to search through the field to find the matching value?

[orange]

My first recommendation is that you tell us more about the business to be supported with/by this proposed database. Please use simple English, no database jargon.

Second is to review Normalization. There is no need to use multivalued fields in Access. You can create intermediate/associative tables or other constructs that adhere to established database practices and can be used with other DBMS. MVF is a construct by M$oft specific to Access, and I'm sure with good intentions, to help new users from the realities of relational database concepts, theories and practices.

Good luck.

Here is a link to more info on database planning and design.

[gwboolean]

Not a problem. This is part of an application for use with medical devices and pharmaceuticals. This particular application deals with parts and assemblies made by the user of the application. each of those parts/assemblies (I call them items) are either made by the user or bought from a vendor. A single vendor might provide the part/assembly for several different items. Therefore, each item supplied by a vendor has the ID # of the item recorded into a specific field of the Vendor table. Since any vendor might supply the same thing to a number of different user items then that field must contain the ID # for each of user items that it supplies.

What I want to do is compare the Item ID to the values contained in the vendor field and if one of the values match the item ID then the record from this table will be displayed in a datagridview.

Does that better explain what I am after?

I will look at the normalization you mentioned and see if I can make it work for me.

Thanks.

[orange]

This is what I'm "hearing":

-Your application is about: medical devices and pharmaceuticals
-You deal with parts and assemblies which are either made by the user or bought from a vendor.
-each of those parts/assemblies (I call them items)
-A single vendor might provide the part/assembly for several different items.


So there are Parts and Assemblies. What exactly differentiates a Part from an Assembly?

You combined Parts and Assemblies into a new "thingy" called Item. Seems to imply that there is NO difference in Part vs Assembly. Then why are they referred to separately?

Is an Assembly some combination of Parts, or is it a separate thing?

A part/assembly may be for several different items???? Seems there is a difference between Part, Assembly and Item--please describe each such that the difference is clear to someone not familiar with your environment

You indicate that some are made by the user. Who exactly is the user?

You also said that some are bought from a Vendor(s). Do Vendors refer to the same Part in the same way?

Can you give us an example or 2 of Parts and Assemblies?

[gwboolean]

Orange,

I scanned through the information on normalization and assuming my understanding is correct, it will work for what I want. However, what I would end up with is a vendor record for each item that the vendor supplies. Assuming I am correct, this is fine, but I would like to have only one vendor record with the ability to tie it to each item that it is associated with.

In the past I have had text fields, using ACCESS, that I could search through to find a specific text string, if it existed in the field. It was so long ago that I no longer remember how this is done and currently I am only using the database and running it using Visual Basic. This is where I came up with the idea of using a single text field that held nothing but a series of Item ID numbers delimited with a comma (I am not wedded to comma delimiters and am willing to consider other delimiters).

I am still looking through the other information you provided.

[orange]

Seems we posted at almost the same time. My latest was more about analysis of the business and the entities involved.

Regarding Visual Basic--are you using vb.net or vba? Your use of multivalued field tells me you are using Access.

Normalization is applicable to all/any relational database.

A Vendor may supply 1 or Many Items (Parts and/or Assemblies)
A Part may be associated with 0,1 or Many Assemblies

[gwboolean]

A part is nothing more than a single item. An assembly is made up of some number of parts. The user might buy a part from a vendor for their own assembly process, or they might buy a whole assembly and either sell the assembly or use it part of a larger assembly (in which case it would be considered a part). Actually, I have no need to differentiate between a part of an assembly and they are not generally referred to separately. Any differentiation would be useful information only in a table where the build actually is defined (an MRP/ERP table for instance). All I am after is to be able to find those vendors that supply something associated with a particular user item (which can be a simple part or a complex assembly).

Bottom line, is that at this level there is no distinction between a part or assembly. The only required distinction is that some thing is supplied by a vendor for aspecific user item.

The user is the person using the application. Vendors have their own ID numbers and information for what the sell to the user. So in this database the user buys an item from a vendor (item can be anything at all). The item entered into an item table and is then provide with an ID number from the user and information about the item and it's usage is entered into that record. Additionally, there is another table that contains vendor information about that item that is bought. This vendor supplied item might be used in more than one vendor item. e.g.


Assembly A consists of Chemical One, Chemical Two and Chemical Three. Each of these chemicals are items in the item table.
Assembly B consists of Chemical Five, Chemical Six, and Chemical two. Again, each of these chemicals are items in the item table.

To begin with There are two ways that assemblies can be treated in the application. There can be a separate table for assemblies and a separate table for parts/items or they can all be placed in a single table. I will probably eventually go with a table for the assemblies and one for the parts making up the assemblies, since this will work better with the MRP/ERP application that comes later.

So let's consider Chemical Two, which is used in both assemblies and not be concerned about the assemblies themselves, since they occur later and at this point we are only interested in the items/parts and who we get them from.
So for now I have the item/parts table that contains Chemicals One through Chemicals Six. In the case of Chemical Two we find that this item is provided by more than one vendor (That is usually the case with most items whether they be chemical, electronic, etc.). So, at this point, I would like one vendor record for each item the user buys and one item record for each item, which would mean that one of the tables requires a text field that contains identifying text strings that can be searched and filtered.

An item record might look like this

Item name Item ID Other Fields information
Chemical Two 101000555

A vendor record might look like this

Vendor Name Item Name Item ID Other Fields
Jebson Chemical Co. Chemical Two 101000555
Dinky Chemical Co. Chemical Two 101000555
Jebson Chemical Co. Chemical Three 111001876

Actually, I can see the solution now and it is not what I thought. You really helped me in making me explain this all out. The solution is that when I look at what the tables need to look like (before coding for VB) there is NO need to have multiple values in the Item ID field. It has become obvious that there has to be a one-to-one relationship between the items table and the vendors table and that a vendor might be referenced many times in the Vendor table, once for each item they provide.

Thanks Orange, this exercise was really helpful.

[orange]

Here is a draft that may help. You can ignore the data types in the fields. You can build on, change or whatever. You can add fields to tables etc as you need to.