Suppliers Management database

[Ciprian77]

Dear all!

I need your invaluable help!
I am working for a small food distribution company and I must do urgently a database with all my suppliers (max.100 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.

I have started to design the database, but...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 and to generate the reports?

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

PS Having in mind that my .accdb exceed 2 Mb I can send directly by email ...

 

[Uncle Gizmo]

You need 3 tables Supplier, Item, Availability.

tblSupplier:-
Supplier ID, Supplier Name

tblItem:-
Item ID, Item Name

tblAvailability:-
Supplier ID, Item ID, mthFrom, mthTo,
189, --------5388,------6,------9

Or you could do Availability Table like this:-
tblAvailability:-
Supplier ID, Item ID, StartOrEnd, Mth
189, --------5388,---Start,------6
189, --------5388,---End,-------9

 

[Ciprian77]

Uncle Gizmo - thanks for your feed-back; just locked on your old posts
I have table Suppliers (with contact info), table Items ( with items listed), but I don't know how to design the new table Valability and to related with other tables, because some items could be available in different periods of the year (for ex. I can buy tomatoes from ES in Dec, Jan, FEb and I can buy tomatoes from GR on May, June have 2 different Tomato items?
Can I send you on email my database in order to see how can we do?
Many regards!

 

[Uncle Gizmo]

>>>Can I send you on email my database in order to see how can we do<<<

I don't have the time to look at DB's.

 

[Ciprian77]

Uncle Gizmo I understand you; thanks for feed-back
Somebody else can help me?

 

[RainLover]

It might help if you posted a pic of your relationships.

Your actual Database is no good to me as I use Access 2003.

 

[GinaWhipp]

Ciprian77,

I received your eMail and fortunately there was no attachment. I say fortunately because it is better if you post it here in the FREE Forum so everyone can benefit. (My eMail is reserved for paying Clients.)

Now, that said, please post what you have so far here. I can examine any version you upload so no worries there. If the database has sensitive information please remove and put in some dummy *play* data.

 

[Ciprian77]

RainLover - tks for your kindness but I have not Access2003
regards,
c

 

[Ciprian77]

Dear Gina!
Thanks for your feed-back.
Please find attached my attempts to build a database...unfortunately I am beginner in Access and I don't know how to define the tables or how can I do the relationships in order to get the final results.
If you can help me with my request I will reward from my pocket - just let me know how much worth your job in order to know if I afford it.
Wait for your feed-back!
Regards

 

[RainLover]

RainLover - tks for your kindness but I have not Access2003
regards,
c

You may not have Access 2003, but built into the Version that you do have is the tools that will allow you to convert.

Search HELP within access.

 

[Ciprian77]

RainLover,
Tks for idea; please find attached my database. As I told you, I am beginner in Access and perhaps the relationships are not good.
thanks in advance

 

[GinaWhipp]

Ciprian77,

Forum = FREE

So, no payment required as long as it stays here in the Forum (and I can answer in my spare time).

I have downloaded both samples and the SMART-FV.accdb is incorrect. Naming the fields using the Products names means everytime you add or subtract a Product you need to amend the table and it's set up like a spreadsheet not like a database table...so just scrap that one.

Database1.accdb is correct with some minor changes. I attached a sample for your perusal.

I created a query showing how you can prompt for the months your looking for.

Based on your first example I see you are new to Access. Here are a few links that might help...

Jeff Conrad's resources page...
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page...
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP)...
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials...
http://allenbrowne.com/links.html#Tutorials

UtterAccess Newcomer's Reading List
http://www.utteraccess.com/forum/Newcomer-s-Reading-List-t1998783.html

Sample data models...
http://www.databasedev.co.uk/table-of-contents.html
http://www.databaseanswers.org/data_models/

Naming Conventions…
http://www.access-diva.com/d1.html

Other helpful tips…

Setting up a Model Database
http://www.access-diva.com/d11.html

My Database Standards...
http://regina-whipp.com/blog/?p=102

Pay close attention to the one labeled *Naming Conventions* as the way you had your fields named will make life difficult as you develop your database.

 

[RainLover]

RainLover,
Tks for idea; please find attached my database. As I told you, I am beginner in Access and perhaps the relationships are not good.
thanks in advance

You do have some learning to do which needs to be done by your own research together with people here like Gina.

A free tutorial written by Crystal (MS Access MVP)...

This is a good referral that Gina posted. It is a small book that will answer most of your questions as a beginner.

Have a read and when you have a particular question post that and we will help out.

 

[Ciprian77]

Thank you RainLover!