New Parts and Supplier Database (1 Viewer)

[hullstorage]

Hi all, using access 2010.
I am trying to setup a parts and supplier database that is currently done using a spread sheet.

Each part may have upto say 5 suppliers with its own price.

I then view the prices and use the lowest quote.

Prices are checked and updated on a monthly basis.

What is the best way of creating tables, relationship and forms for this task.

Here are the fields below and I have attached a sample of the speadsheet.

This only need to be a very simple database as it is solely used for comparing prices supplied for each part from each supplier

PARTNO
DESCRIPTION

SUPPLIERNO
COMPANYNAME
COST

Many thanks

 

[tehNellie]

If that is a representative sample of your data I'd be inclined to leave it in Excel to be perfectly honest. As a learning excercise it's possibly a reasonable place to start, but Excel is really good at this sort of thing and chucking Access at this is a Hammer to crack a nut situation in my opinion.

If you're adamant (Prince Charming!) that you want to do it in Access then that sheet suggests 3 entities:

Parts
Suppliers
Prices

You've got your basic information from the spreadsheet, so what else do you need to know about each entity?

 

[peregrin]

It looks like between suppliers and parts you need a linking table (sometimes called join tables, junction tables, or associative tables). This "Suppliers_Parts" table will hold the part number and supplier number as a composite key, and the price of the part from the supplier. It's also a good place to store something like the supplier's part number (as opposed to your internal part number that you use in your system), minimum quantities, or things that are specific to that supplier-part combination.

 

[gemma-the-husky]

unlike tehnellie, i think this sort of thing is a good candidate for a database (and probably an effective learning environment)

no doubt at the moment you have a single spreadsheet, with different columns for the suppliers, which may be different for each part.

so if you had to get a list of all the prices for a single supplier, it would be hard. If you needed to track price changes, it would also be hard.

so as peregrin says, you need 3 tables

parts
suppliers
parts-suppliers junction table (which will contain the price for the particular part-supplier combination)

now, if you need to then add different stuff (like the effective date, and a price history, you can build on this basic structure. maybe you can rank the suppliers for a part in order of preference. maybe you add in stuff like lead-in-time, minimum order quantity, and stuff like that. once you get used to thre power of a database, the possibilities are endless

-----
you do have to "lose" the spreadsheet thinking. you need several tables. you do not limit yourself to 5 (or any number of suppliers. you will rarely, if ever, see all your data at once.

and something you often see in spreadsheets - the process of referencing a cell to another cell on a different row, is in general something to be avoided in a database.

 

[tehNellie]

It's a candidate, but in a 5-6 column spreadsheet to compare prices, is it really worth the effort to convert that to a database?

If you want to make the case that by putting those 5-6 columns into a database you add the capability to know a lot more information about your suppliers, the flexibility to add more, to exclude those from this particular price analysis and so on then sure, I'm not going to argue with you on that front.

If that spreadsheet is a small sample of 1,000s of products and lots of suppliers then, again, I won't argue that it might be better to convert it to access. It depends. Lots of people stick with Excel when Access would give them more control and flexibility, likewise people also take what could easily be managed in Excel and decide they need a full blown database application.

Horses for courses as they say.

 

[RainLover]

I agree with Dave.

With a Database you can create a history and view the cost of a product over time.

Record info on your supplier.

Record delivery times.

Track returns.

Over time you will have so much information ot your fingertips the Boss will be most Impressed.

The advantages go on and on.

The important thing at the moment is that this is an opportunity to learn.

My suggest is to design as best as you can then post what you have and ask for opinions.

When you post a Database I suggest you convert to 2003 as more people are still using that rather than 2010.

 

[jdraw]

I agree with Dave and Rainlover. If you are going to use Access, then I suggest you review the videos below to understand/refresh the concepts involved.

Here are free video tutorials that you may find helpful.
http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

You can go back and watch these as often as you want.

 

[tehNellie]

*shrugs* I see a 6 column spreadsheet comparing a few columns of numbers to see which one's lowest. Doddle In excel, easy to use, easy to manipulate, zero setup and minimal matainence if that example is indicative of the current 'system'. If that's all it's ever going to do why spend days/weeks creating a database that 9-10 ends up being a spreadsheet with a form on it anyway?

If you want to take that functionality and make it part of an all singing, all dancing database system that holds information on your suppliers, price history, stocks, invoices etc etc etc and learn about databases while you're at it that's fine.

Like I say, horses for courses.

And I think suggesting to someone 5 years+ after Office 2007 came out that they should use 2003 format is plain silly. If people want to posit themselves as experts ready to dish out advice they should have some knowledge and access to a format that's been kicking around 5 years.

 

[jdraw]

tehNellie, I often agree with your comments, and even here you may have a great solution. But I recall some of the previous posts by hullstorage. He's using Access and has been for a while, and is till not conversant with concepts. My suggestion was to go back and review/get acquainted with the concepts. I don't care if he's using Access, MySQL or any other. And I wouldn't suggest anyone has to move to the latest version of any particular software - especially database packages. I spent many years finding bugs in the latest (supposedly tested versions of commercial software). I get your point but I have 2003 ( a hobby at best these days) and don't intend to move just because newer versions are out there.

As for hullstorage take a good look at all of his posts - don't take his last request as his only concern/issue/opportunity.

As for attaching databases for comments, I agree with Rainlover that attaching in mdb format (2000-2003) will make you database accessible to a broader audience.
Just my $.02.

 

[RainLover]

*shrugs*
And I think suggesting to someone 5 years+ after Office 2007 came out that they should use 2003 format is plain silly. If people want to posit themselves as experts ready to dish out advice they should have some knowledge and access to a format that's been kicking around 5 years.

If this is in anyway directed at me then I would suggest that you reread what was writen.