Datasheet and lookups...

[Joe]

Here's what I want to do.

I have two tables Materials and Manufacturers.

The relationship is that a Material is made by a Manufacturer (Material
Many, Manufacturer One). So the client want to Datasheet view to sort and
edit
the Materials and verify that the information is correct. They have come up
with a scenario where they want to change the Manufacturer. However I am
no sure how to do this. Can I make the Manufacturer field a Lookup within the
datasheet?

Any thoughts/pointers would be appreciated.

 

[Damon Heron]

Looks like you have a many-to-many relationship instead of a one-to-many.
(more than one mfg can supply the same material).
One solution is to add a "junction" table - like tblMfgMaterial, that has a
one-to-many relationship with the Mfgs, and a one-to-many relationship with
Materials. The table would consist of two foreign IDs, to the Mfg table and
the Materials table.
As an example, suppose two mfgs supply one material. The junction table
would have two entries, pointing to the Mfg table and the Materials table.
From the view of the Material, you would have two mfgs shown. From the view
of the Mfgs, you would show all materials that the Mfg is supplying.

Damon