Data Input on Form (1 Viewer)

[nstratton]

I have a form that allows a user to add new suppliers to the database and add all the relevant information. The suppliers are grouped into categories and sometimes one supplier fits multiple categories.

When adding the information, how would do account for the possibility of the supplier being added belonging to multiple categories? The first thought that came into my head was the user would have to add the same supplier multiple times to account for the different categories. Surely there is a better way to go about it than that?

 

[Minty]

You should have a categories table and a Junction table with the supplier ID and Category ID to store the joining of the two together.

SupplierID, CategoryID
1 , 1
1 , 2
2 , 2
3 , 1

etc...

 

[nstratton]

Right, I've got that. So are you saying after I add the first category, "save it" via button or whatever then go back and add the next category?

 

[Minty]

I'm assuming your categories are a relatively fixed list. So create them in the Categories table.

On your Supplier form add a sub form with the Junction table with Categories listed as a drop down from the Categories table, and the supplier code as the child field.

Once your supplier is saved you can add as many categories as you want per supplier.

 

[Alvarogue]

I am lost here.. how do you create the junction Table?