Filter Code Help

[Desitech]

I am not sure how to go about this. Any help would be greatly appreciated. Here is what I am trying to do:

I have a table with records. I want to associate each record with multiple associations.

For Example:

record 1 A-12316 belongs to KI2001, KI4000 and KI9200.
record 2 A-06542 belongs to KI2001 and KI4000.
record 3 A-11523 belongs to KI4000

(I have a total of 33 possible associations)

Now I have a form "FrmOptions " with a command button. I want to be able to Click on the Command Button and perform the following:

OpenForm "FrmParts" (that is linked to a table "TblParts" with all the part records like A-12316),

Next, Filter the form by Association. In otherwords, Filter the "FrmParts" Form by only showing the records that have KI2001 associated to them.
Close the Form "FrmOptions ".

In this example, only the A-12316 and A-06542 would be returned on the form. Also want this form to be read only.

I was thinking of adding 33 fields to each record as part of the record to the Parts Table "TblParts" and populating the fields with the [PRODUCT].

Field titles
PRODUCT 1
PRODUCT 2
Thru
PRODUCT 33

Or would that be to cumbersome? Any Ideas?

[FishVal]

@Desitech
The only idea you need is an idea about Database Normalization and Table Structures

Kind regards,
Fish.

[NeoPa]

It certainly would.

Consider creating an association table containing two fields. One being an FK to the first table you mention (You didn't refer to it by name - unless it was tblParts. Not clear), and the other to a table of the item (I'm expect this is [PRODUCT] table). These two keys together would make up the PK of this association table, and could be linked in quite flexibly, including for the way you need currently.

[Desitech]

I am pretty new to this. What is a FK and a PK? I am not sure how to have 1 record from the TblParts link to Many records on the new table TblProductAssoc iation.

[NeoPa]

@Desitech
Sorry. My bad.

PK = Primary Key.
A unique identifier in a table. Each record has a value which is unique only to that record.

FK = Foreign Key.
A value which is (matches) the PK of a record (generally not itself). This may be in any table.

Consider a table [tblFamily]. It may contain a [Surname] field, which could be a PK for the table. A [tblPerson] table may include a [Surname] field too. In this case though, it would be an FK. Certainly not unique. It could be used as criteria for a WHERE clause though, to select and show all members of a particular family.

Does that make more sense now?

[Desitech]

So if I have a table entitled tblParts and the Primary Key is on the Part Number field and I make a table entitled tblProduct, (Listing 33 Products in 33 Fields, how does one part record get associated to 1, some, or all of my 33 Products? How do I assign a Foriegn Key? I think I am still pretty lost.

[Stewart Ross]

Hi. My colleagues Fish and NeoPa have given good advice on how you need to proceed, here. The fundamental problem you have is that in order to design useful tables with a relational database it is absolutely essential to understand the idea of table design and normalisation - which is what the article Fish linked for you in post # 2 is about.

Essentially, relational databases comprise sets of tables, many of which relate to each other in 1-to-many relationships. For example, your product and parts tables link this way, as for each product there can be one or more parts, with no limit on how many. Actually, the 1-many relationship is with product and another table which could be called product-parts. This is a linking table that decomposes what is in reality a many-to-many relationship between product and parts, as for each product there can be many parts, and a part is a component of many products. Many-to-many relationships cannot be implemented directly in a relational database; the many-to-many relationship has to be decomposed into two 1-many relationships, implemented by using a third, linking, table along with the two original tables.

In your product-parts table the primary key of the parts table is carried forward as a foreign key, as is the primary key of the products table itself. There will be many rows in this link table for each of your unique products, as each product comprises many parts.

There is no substitute for understanding the process of normalisation - without it you will not succeed in using a relational database efficiently, if at all.

I'd strongly advise you to read the article referred to, and to work your way through introductory texts on database design. The skills of normalisation of table structures are vital when working with relational databases and simply cannot be skipped.

You will find good examples of 1-many relationships within the Northwind database which ships with Access. It would be useful to you to review how that sample application's tables are built. Even so, you need to do the basic ground work on table design for yourself - there is no substitute or ready-made answer which can help you except by pointing the way.

-Stewart

[Desitech]

Thanks, I am reading and learning about relationships, and tables right now. I am starting to under stand 1 to many, many to many, and many to 1, relationships. I do apologize for my stupid questions as I am trying to learn access and apply it for work all at the same time, and trying to look for quick answers. I do greatly appreciate everyone's help. I have everything working in my Database from all your input. So now, once I get this last part done I will be finished...unti l another project comes up. I am not a programmer by trade. I am primarily a user trying to be a hack programmer. From going thru the reading, it looks like I have a many to many relationship (many parts to many products) and (many products to many parts). This means I need to use a join table. How does the join table get populated? I have already made the one to many relationships from parts to join table and Products to join table. Do I need to enter every part multiple times if used with multiple products?

[Stewart Ross]

In an Access implementation of a normalised set of tables for the products and parts you would, firstly, set up the tables and implement the relationships between the keys, enforcing relational integrity. Secondly, design queries which provide what is known as a view - behaving like a virtual table - of the underlying data, allowing for user-centred ordering of the rows. Thirdly, design a form-subform combination based on those queries which will allow you to specify a product (on the main form) and select its parts on the subform. The subform's record source will be the product-parts table not the parts one, for the reasons mentioned in my post about decomposing many-to-many relationships.

You will see examples of the use of form-subform structures which populate link tables in the Northwind example database.

Good luck with your project

-Stewart

[NeoPa]

Stewart, you always explain things so well.

It makes my post #3 look almost curt. I'm comfortable now that the issue is as clear as it could be.