Results 1 to 4 of 4
  1. #1
    Schae235 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015

    Primary and Foreign Key Question.

    So I have a table 'TableA' that has unique part number on it. 'TableB' is a certain category of parts. 'TableC' is a different category. What I would like to happen is every time a new part is created on 'TableB' or 'TableC' a unique record is created on 'TableA' and pushes the part number to the table. That record in TableA would then be 'blocked off' and the next record that would be created would make a new unique record in TableA

    I am sure that this is an Access 101 question but I can't seem to figure it out.


  2. #2
    RayMilhon is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Southern California
    If table B and Table C are categories of Table A. Why would Table B and Table C have the individual part number. Also why 2 Tables with the same type of data.

  3. #3
    Rawb is offline Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2009
    It sounds like you're using a non-normalized Table structure. If both TableB and TableC are categories of items in TableA, then you should probably be using a structure more like the following:

    • Table PartCategories
      • Column Category_ID - The unique ID for each category of part. Autonumber, Long Integer. Primary Key.
      • Column Category_Name - The "friendly" name of each category of part. Text String.

    • Table PartNumber
      • Column Part_ID - The unique ID of each part. This Column is for use internally by the system and is not necessarily the official "part number". Autonumber, Long Integer. Primary Key.
      • Column Category_ID - The ID of the category of part this is. Long Integer. Foreign Key -> PartCategories.Category_ID.
      • Column Part_Number - The user friendly "part number". Text String.

    If, for some reason you have to use your existing structure, you would almost have to use VBA code to do what you want.

  4. #4
    Schae235 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015
    Rawb and RayMilhon, thank you for your replies and I am sorry for the delayed response. I was on vacation for the holidays.

    RayMilhon I want to keep a list of the part numbers in TableA but we have multiple different categories of part numbers where I want to be able to keep different information for each category. I used to work in a produce shop so an example using that would be they stock both apples and grapes and want to keep different information on each (Apples: size, color, taste, price, etc./ Grapes: texture, price, firmness, size, etc.) but we still want to have a uniform number to order each item by. So both apples and the grapes would have P#### numbers.

    Rawb, I think this solution sounds like it might work. I will have to attempt it this afternoon to see for sure though. I am relatively inexperienced in Access so it involves a lot of fumbling around right now.

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

Similar Threads

  1. primary and foreign keys?
    By themebuddies in forum Access
    Replies: 1
    Last Post: 12-14-2014, 09:23 PM
  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