Results 1 to 4 of 4

question about keys

  1. #1
    johny is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    1

    question about keys

    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.

  2. #2
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,412
    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

  3. #3
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    655
    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.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,016
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. primary/foreign keys
    By st1300 in forum Database Design
    Replies: 38
    Last Post: 12-04-2011, 01:27 PM
  2. Need help with foreign keys
    By acdougla17 in forum Access
    Replies: 1
    Last Post: 09-26-2011, 03:50 PM
  3. Tab or Enter keys
    By rwatkins in forum Forms
    Replies: 3
    Last Post: 05-11-2011, 03:22 PM
  4. Replies: 0
    Last Post: 09-06-2009, 07:27 PM
  5. How to make use of Function Keys (F1, F2 etc)
    By rommelgenlight in forum Access
    Replies: 1
    Last Post: 05-15-2009, 02:51 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums