Relating records in same table

[juasmith]

I am trying to do a database for a small flooring company. I have created a table (products table) for all the flooring products they carry. Some of the moldings (t-mold, reducer, qt. round) can be used for more than one floor. All the moldings and actual floor are all in the products table. So how do I group the moldings with the floor if they are in the same table. I want to be able to make a form which lists the floors along with their related moldings. This is a head ache for me, I just can't figure it out. Any ideas ??

 

[SforSoftware]

You can create a second table, with two fields, that both contain the ID of the first table. Than you can create two one-to-many relationships, from the first table to both fields in the second table.

 

[Pat Hartman]

To be a little more clear, you are using two tables to create a many-to-many relationship rather than the more normal three. When you create the query, you will need to add the products table to the grid twice and then add the relation table between the two instances of the products table.

tblProduct:         tblRalation:           tblProduct_1:
ProductID ------>FloorProductID
ProductType       MoldingProductID <---- ProductID

ProductType indicates whether the material is flooring or molding and that will control which ID is first in the relation table.