Help with Table Structure/Relationships

[pbolger]

Hi ,
I am trying to put together a simple database to manage supplier contacts.

The info i will be inputting Supplier Name, Country (to be selected from a combo box), Category Name (There might be many of these for each supplier) and Product Name (there might be many of these under each category)

what is the best way to structure these tables and how should the relationships be defined.

any samples/examples greatly appreciated.

Thanks
pbolger

 

[Dugantrain]

This is how I would do this: You have four tables:

SUPPLIERS
Supplier_ID(autonum)
Supplier_Name(text)
Country(text)

CATEGORIES
Category_ID(autonum)
Category_Name(text)

PRODUCTS
Product_ID(autonum)
Product_Name(text)
fk_Category_ID(Number)

SUPPLIERPRODUCTS
fk_Supplier_ID(number)
fk_Product_ID(number)

Tie Category_ID to its corresponding fk_Category_id
Tie Supplier_ID to its corresponding fk_Supplier_id
Tie Product_ID to its corresponding fk_Product_id

Doing so will give you one Category for many Products, one Supplier for many Products, and one Product for many Suppliers. The combo box with which you would look up countries would be on a form that you base off of these tables for data entry. Hope that helps get you started.

 

[pbolger]

Hey thanks,

any chance you could do up a quick form for entering data to these tables. not sure if i need one/two subforms. not sure which tables i should link text boxes to - eg - product table or supplierproduct table. do i need some look up query too?

 

[jeremie_ingram]

I would also include a seperate table containing the coutry information. You could use a combo box lookup to retrieve the data, and you would have a source to add/delete countries as neccessary.
As soon as I get an opportunity, I will attempt a GUI for you.

 

[pbolger]

cheers thats a lot.

 

[pbolger]

thanks a lot rather !