Multiple entries

[Jane]

I'm sure there must be an embarrasingly simple way of allowing users to add
more than one entry to a field. E.g,
a supplier Contact will stock many ranges, but how to add a list to a simple
contact database?

I've created another table tblProducts, linked it to a field in
tblContact... and now I'm stuck.

I just want to add a button to the Main Contact form showing which ranges
the supplier stocks and allows the user to add more, which will update the
list in tblProducts, too.

Can someone point me in the right direction?

 

[Van T. Dinh]

Actually, it is NOT recommended. The Relational Database Design Theory
specifies that Fields should store a *single* item of data and NOT a list
(technical jargon: "Fields must be atomic." as per the First Normal Rule).

It is very much depends on what you need in your database.

If a Product can only be supplied by one Supplier, the you can simply add a
Field "SuppliedBy" in the Product Table to specify who the Supplier is.
This is a One (Supplier) to Many (Products) relationship.

If a Product can be supplied by many Suppliers (and conversely, a Supplier
can supply Many Products), you have a Many (Suppliers) to Many (Product)
relationship. This is modelled in the database by an additional Table
tblSupplierProduct that links each Supplier to the Product the supplier
supplies. Thus, if the Supplier supplies 10 different Products, you will
have 10 Records in this Table, one for each Supplier - Product pair.

Then you will need to set up your Forms to handle this set-up.

Suggest you find out more info. on the Relational Database Design Theory
(and the Database Normalisation technique) and apply these to your database
before you proceed with the later stages of your database design as this
(the Database/Table Structure) is probably the *most* important step.

 

[Jane]

Ahhh, of course - I need a 'connecting' field. I forgot the basics! Thanks
for the advice.