Suppliers Management database - Huge help need it!!!

**Danny77** · 06-30-2014, 03:30 AM

Dear all!

I need your invaluable help!
I am working in a food distribution company and I must do urgently a database with all my suppliers; each supplier can deliver at least 5 items, but with different availability ( for example Supplier 1 can deliver oranges in march, April, may, tomatoes in Jan, Feb, Mar etc...and Supplier 2 can deliver oranges in Oct, Nov, dec, Tomatoes in June, July, August ...aso). AT the end, depending by current period selected (ex. June) and/or by item requested I need to extract the suppliers which fulfill the requests.

Being too many variables (one supplier can deliver many items in different periods) can you give me an idea / solution in order to design this database?

I am waiting for your feed-back asap - With great respect!

**Kaper** · 06-30-2014, 04:00 AM

It seems it is really database with possibility of growning large - so why not use database software?
If stick to excel - think of applying typical database approach: separate tables for suppliers and for products then table productsfromsupliersinperiods which have fields supplierID, productID, startperiod, endperiod, probably some other firlds - price and volume for instance

With that approach you can manage reasonably large database in excel using just INDEX(MATCH , SUMIF(S) and COUNTIF(S). Probably at some point it woud be wise to use Pivot Table as report pre-preparation tool.

**Danny77** · 06-30-2014, 04:08 AM

Kaper,

I am working in a small distribution company and we can not afford a database software.
The number of items is maximum 20, the number of suppliers will not exceed 75...the main issue is that I have no idea how to design this database. It is in my intention to use Pivot Table but I have not idea how to design the database in order to get the list with the companies able to deliver the requested item on current month...Do you have an idea?
regards

**Kaper** · 06-30-2014, 04:57 AM

Play with my suggestion about 3 tables.
To easily create a list you can also consider a filter.
Impotrant tip: do not put months as month names. Use either excel dates or month numbers. It will make filtering (as well as mentioned in previous post formulas) way easier then with monthnames.

**Danny77** · 06-30-2014, 05:01 AM

Thanks for your feed-back! In order to understand better your idea, can you attach me a template? (I ask you apologizes if i ask you too much)

**bigroo1958** · 07-03-2014, 08:10 AM

Hi Danny,

Here is my take on your database issue, this is a start, you can expand on it, but it will give you a starting point!!
I have a list of Fruits and Vegetables and have thrown in random available months when they may be due, this of course and be changed to suit your needs, this is just an example!

Good Luck on your project!!