form question

[David]

Hi,

I'm new to Access and had a question. I have several tables with
"Products" being my main table and my other tables we'll call "Material
A", "Material B" they all have a relationship with my Products table. My
question is when making a form which table am I suppose to bind the data
to? If I have a new product of course that field on the form would enter
into the products table but if that product uses a new material not
listed in the Material A table would I have the Material A field on the
form enter the data into the Material A table? If so would it fill in
the field in the products table that related the two tables together?


Thanks,
David

 

[Jeff Boyce]

David

Not sure I understand your situation clearly, but I do understand that you
have multiple tables, each with similar data (MaterialA, MaterialB, ...).

While this might be a reasonable design ... for a spreadsheet, you'll find
you are not able to easily use the strengths Access offers unless you spend
some time normalizing your data structure.

Anytime different categories of something (e.g., "material") are placed in
different tables, or even as repeating fields in a single table, your data
(and application) may benefit from further normalizing.

For example, you could have one table for Product, one table for Material,
and one table that holds only the ProductID & MaterialID for valid
combinations. This way, any newly-added product can go in its table, even
before you know what material(s) is used. And the addition of a new
material won't affect your table structure -- just add it to the Materials
table. Then join the new product(ID) and material(ID) in your third table.

Regards

Jeff Boyce
<Access MVP>