Linking two tables

[leungwai142]

I am new to MS access, sorry if i am not making my questions clear enough.

I created two tables which are TabMaterial and TabProduct.

TabMaterial contains a list of material as follow:

• Name, Type
• Blue Plastic, Plastic
• Red Plastic, Plastic
• Blue Glass, Glass
• Red Glass, Glass
• Etc

TabProduct contains the products as follows:

• Product, Material, Price
• 001, Blue Plastic, 10
• 002, Red Plastic, 20
• 003, Red Plastic, 30

What can i do if i want to create a column in a query, which automaticly shows that if TabMaterial.Type is Plastic, product 001 is plastic as its material is blue plastic. as so as if TabMaterial.Type is glass.

So that it shows

• Product, Material Type
• 001, Plastic
• 002, Plastic
• 003, Plastic
• Etc.

Thanks in advance.

 

[leungwai142]

Sorry. I missed one point. I know it can simply solve by creating a relationship. But in fact. Each products contains more than one material. so TabProduct should be the case:

Product, Material1, Material 2.

• 001, Blue Plastic, Red Plastic
• 002, Red Plastic, Blue Glass
• 003, Red Glass, Blue Glass

And this is wt i want to show in the new query:

001, Plastic
002, Plastic & Glass
003, Glass

 

[Norie]

The data you've posted isn't very clear.

Can aproduct consist of one or more materials and a material be used for more than one product?

eg Product 001 - Blue Plastic, Blue Glass
Product 003 - Blue Plastic, Red Glass *

If so that's a many-to-many relationship between products and materials.

You can represent that relationship with another table, let's call it ProductMaterial.

Each record in that table would have the primary key for the product and the material as fields.

* The data I use in the example is a simplification of what you might have.