Results 1 to 6 of 6
  1. #1
    JohnnyChimpo is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2015
    Posts
    3

    Primary Key/Foreign Key question


    Hey everyone,

    New member to the forum, a bit of a novice when it comes to Access. I'm using Access 2007 and occasionally 2003. Currently, I'm helping someone by making a small database for them; the database is to store sample records. There are 6 different categories of samples, so I have 6 separate tables to record the information. Each sample has a unique ID, which I have set as the Primary Key for each table, because I don't want anyone to be able to enter the same ID number for two different samples. It works fine within each individual table, but I can enter the same sample ID in a different table. So I want to link it somehow that the Sample ID primary key is the same primary key across multiple (up to 6 tables). Is this possible?

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Have you considered a table called tblSamples and a table called SampleCategories?

    tblSamples
    SampleId
    SampleCategoryID FK
    OtherInfo about this sample

    tblSampleCategories
    SampleCategoryID
    SampleCategoryName
    SampleCategoryDesc

    with category info such as
    Code:
    CategoryID  1
    SampleCategoryName Cat1
    SampleCategoryDesc Cat 1 is .....blah blah..
    
    CategoryID  2
    SampleCategoryName Category2 Name
    SampleCategoryDesc Cat 2 is .....BBlah blaHH.....
    Each Sample is uniquely identified by the sampleID, and indicates the category of sample using CategoryID

  3. #3
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    EDIT: I'm a slow typist.

    I suggest you create a table that is dedicated to the categories. Since you mentioned there are 6 categories, this table would have 6 records. If a new category is created/realized at a later date, you simply add another record. I would use the Autonumner data type for the Primary Key column of your tblCategories. This table will probably have two columns, only. One column for the PK and another for the Description (type Text).

    When a new sample is created, create a new record in a table dedicated to samples. This table, let's call it tblSamples, would also have an Autonumber column as its PK. Additional columns would be Foreign Key columns (and maybe a Date/Timestamp). For instance, the value of Primary Key fields from tblSamples would be stored in a Foreign Key column in tblSamples. Foreign Key columns that relate to PK's of type Autonumber will need to be a Number type Long.

  4. #4
    JohnnyChimpo is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2015
    Posts
    3
    Thanks for replying.

    Its certainly a possibility to do it that way. However, with each sample having multiple components, i felt it was a more simple and clean layout from a users end to have separate tables. I was hoping there was a way to link each individual tables Primary Keys so that they couldn't have the same value. Each ID is a 6 character field, starting with SS and then 4 numbers after. SS1234 for example. In an ideal world, I would be able to have the field "Sample ID" be the Primary Key in each table.

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Why separate tables? Can you give us more details, even some example data to help readers understand.

  6. #6
    JohnnyChimpo is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2015
    Posts
    3
    I don't know why, is the honest answer. I felt it was the way to go, but I'm pretty much a novice at this thing. I'm good with computers overall, and I made a database in Access in college for a project in my second year, which was over a decade ago, but have never used it since (so I say I'm rusty is an understatement). I've kind of been roped into helping out a friend with this, and I have an inability to say no!

    There is a good chance I've structured all this the wrong way, or a less streamlined way than possible. I'm open to changing it all if I need to. I'll give you an idea of what we want - we know from a users end point of view what we want and have kind of reverse engineered from there.

    So, we would like a Home Page were the user has two options buttons. One button is a "Search" button which brings you off to a variety of search options. The other button is an "Add" button where you can enter new data into the database. After clicking the "Add" button you get to a new screen with 6 big buttons on it, each button a different category of sample. Click a sample type and you get to a page where you can input the information. We want to keep it as simple and as straight forward for the end user, so there is a lot of drop down menus (lookup wizards) to help guide the user. Each sample has such variance in its components (like ingredients if you will) that we felt the user interface was too cluttered with irrelevant fields depending on which sample you were entering - so we went with the separate tables approach. So the forms for each table would be more streamlined. The Primary Key is the Sample ID, which is a 6 character ID, starting with "SS" and 4 digits after it; SS1234 for example. So I need to either automatically generate a new SSxxxx number everytime a form is entered, or find a way to link each tables Sample ID field to one another to prevent duplicate entries.

    Maybe I could just put it all into one table and work from there. Its something I'll look at.
    Last edited by JohnnyChimpo; 10-04-2015 at 07:37 AM.

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

Similar Threads

  1. Primary and Foreign Key Question.
    By Schae235 in forum Access
    Replies: 3
    Last Post: 04-08-2015, 07:03 AM
  2. Primary key/ foreign key question
    By DaveG in forum Access
    Replies: 5
    Last Post: 05-02-2014, 12:42 PM
  3. Primary Key and Foreign Key Question
    By chrisbas in forum Access
    Replies: 1
    Last Post: 05-07-2012, 04:01 PM
  4. primary/foreign keys
    By st1300 in forum Database Design
    Replies: 38
    Last Post: 12-04-2011, 01:27 PM
  5. import the foreign key into the primary key
    By vCallNSPF in forum Forms
    Replies: 3
    Last Post: 01-14-2010, 06: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
  •  
Other Forums: Microsoft Office Forums