question about keys

[johny]

Hello,



I have no experience in using access and after making a table in it that had five fields it asked me to choose one field as a primary key. The problem is that the table (which represents a tyre) can not be described with one primary key.
Lets say i have these fields :
-size
-company
-quality
-date
-quantity
A tyre should be uniquelly defined by a combination of the first four fields of this table,it can not be described with one primary key of only one field.
Even if we change one of the first fields we geta new tyre.
Also the last field called quantity is irrelevant to the uniqueness of a tyre.

So how can i define the keys to do it? Since i am new the simpler way would be best.
Thanks.

[alansidman]

Perhaps a little bit of background reading on database design will help you. Read this and I think you will have a better understanding of primary keys.

http://www.deeptraining.com/litwin/d...aseDesign.aspx

http://forums.aspfree.com/microsoft-...es-208217.html

Alan

[pkstormy]

At a quick glance in your situation johny, it "looks" like the Company (ie. better named: CompanyName) would most likely be the primary key. This would then make it a field where you could not have exact duplicates. If you do have duplicate CompanyNames, you may have to concatenate the primary key field with another field which makes it unique. Basically when you establish a primary key, you are telling the db that there will be no duplicate values in that field or those 2, 3, etc.. combination of concatenated fields. There are some rules which Alan posted that you should follow when deciding the best primary key. You don't want to over-do it. For example, you wouldn't want to include a field as a primary key which really doesn't play a factor in the uniqueness of the record.

[orange]

Further to what Alan and pkstormy have said, I would suggest that you write in a few lines describing what your business is. The following is the kind of info that would/could/should be useful.

We build/supply tires(tyres) to local service stations/repair shops. We get our tyres/raw materials from a number of companies.
We hold tyres/parts in inventory at our main warehouse. When our inventory is reduced, we have to reorder from our Suppliers.


From your post, it appears you have Company(s), Tyres and Inventory (quantity)

It's much easier for forum users to respond/advise, if we understand your situation. A Primary key is the method to distinguish one record from every other record in a table. The sites in Alan's links are well respected.

Good luck with your project.

Once you get an understanding of Normalization, you'll appreciate database a lot more.

Here are some links to videos and site that should help. These are geared to database design.

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

http://www.youtube.com/watch?v=lXAGQ8vmhCY ERD Part 2

See What is Normalization here also. The first few topics are well worth the read.

http://www.rogersaccesslibrary.com/forum/topic238.html

For help with access generally, this link has been referenced often
http://www.accessmvp.com/strive4peace/ Lots of Access Syntax, rationale, samples etc.