Design Q?

[deewe]

Hello again,

I'm stuck at a design issue, I don't know by witch side to grab it...

I have the following scenario

• TableItem populated with unique items Item
• TableMaterials populated with unique materials Mats

Each Item can be tied to 1-n Mats and each Mats can be bound to 1-n Item.

I would have used fields with multiples values but later on I need to add a specific Mats quantities to each Item.

So a n to n relationship that'll need a 3rd external table. Right?

The Table would look like: [ItemID] [MatsID] [Quantity]


==> The issue arises when filling in the data <==

When entering a new item I'd like the user to be able to select any number of Mats and add Quantity regarding each.
As a note all Mats infos and choices will be already filled in.

How should I do that?

Thanks for the help!

[deewe]

Found the solution, posting it if it can help others:

So I finally made an unlinked ItemMats table with the following fields:
[ItemID] [MatsID] [Quantity]

• [ItemID] is a combo box that gets its data from the TableItem table
• [MatsID] is a combo box that gets its data from the TableMaterials table
• [Quantity] is filled in by the user.

In the main Form used to fill in the TableItem table I added a subform to the ItemMats.

Not only it automatically display only the ItemMats related to the selected [Item] records but I'm also able do add/update/delete entries on the fly