A life jacket please

[vampire12]

Hello all, I just started to use access 2013 and im really in the first steps. Im in trouble and i cant find a solution coz i don't know how to search for it, i went to YouTube but i have no idea how can i search what im looking for. Please help me a lil bit. I want to create a new ddb.


I have a list with 100 suppliers "Sony, Microsoft, Apple ext..."
I have a list with 500 goods "tomatoes, carrots,ext.." and there is a limit to buy at a time, example "50-100 Kilograms of potatoes from Microsoft, 10 - 500 Kiligrams of potatos from Apple ext..". Now i want to assign each supplier with each of these goods.But not all the suppliers have the goods in stock right now. But possibly in the future they will have it.


In the end i want to run a query and ask
for suppliers that sell potatoes and have it in stock!


Thanks in advance

[euanl]

If I get what you mean have a table for suppliers, (tblSuppliers), a table for goods (tblGoods) then use them as drop-downs in a third table, so you select the supplier from a list, the goods from a list and you can have other fields in the third table eg in_Stock, which would be a tick box. That way your query criteria would be for example Supplier = Microsoft, Goods = Carrots, in_stock = TRUE.

Or am I way off the mark?

[vampire12]

If I get what you mean have a table for suppliers, (tblSuppliers), a table for goods (tblGoods) then use them as drop-downs in a third table, so you select the supplier from a list, the goods from a list and you can have other fields in the third table eg in_Stock, which would be a tick box. That way your query criteria would be for example Supplier = Microsoft, Goods = Carrots, in_stock = TRUE.

Or am I way off the mark?

Sorry my mistake.

In the end i want to run a query and ask
for suppliers that sell potatoes and have it in stock!

I want to run a query asking for potatoes, and gives me all suppliers that have potatoes in stock.

[vampire12]

I don't know how to assign each product with each supplier

i just started with Access and I dont know how to connect each supplier with each product. Do i have to stay and make 500 tables?? 1 table for each supplier?

[ssanfu]

Do i have to stay and make 500 tables?? 1 table for each supplier?

You need to read about database design. Not slamming you, but this is a very, very important subject. If you do not have a good table design, you will have major problems later.

So you have tables "Suppliers" and "Products".

One supplier can have many products
One product can have many suppliers

You need a 3rd table: a junction table.
This is just a suggested beginning:

tblSuppliers
----------------------
SupplierID_PK (Autonumber)
SupplierName (Text)

tblProducts
----------------------
ProductID_PK (Autonumber)
Prroduct_Desc (Text)

tblSuppliersProducts
-------------------------
Sup_Prod_ID_PK (Autonumber)
SupplierID_FK (Number - Long)
ProductID_FK (Number - Long)
InStock (Y/N)


(Note that there are NO spaces or special characters (except the underscore) in the names)


This forum has lots of recommendations on how to dB design. Search this forum for "normalization"
Google "normalization"

[ssanfu]

I don't know how to assign each product with each supplier

If you have a junction table, use a form/sub-form.

[vampire12]

Thank you so much ppl, your so helpful. Problem solved!