Results 1 to 3 of 3
  1. #1
    Dalagrath is offline Only a Man
    Windows 7 Access 2007
    Join Date
    Oct 2010
    Location
    Colorado
    Posts
    45

    Multiple Foreign Help

    I have a bit of a issue here. I am practicing with Access by using it for my Star Wars Roleplaying Game. I have learned a bit about how tables work, but for the life of me I can't figure this out.



    Now, in the roleplaying game you can have up to X amount of different classes, and each class you take can be X amount of levels. For example, let us say I want to be a Level 2 Jedi and Level 8 Noble, in turn would make my "Character Level" a total of 10.

    My question is: Is there a way that I can have multiple ForeignKeys in the same box or will I have to make 100 different tables?

    Here is a picture of my current set up:


  2. #2
    LillMcGill is offline Dagny fan
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Oct 2010
    Location
    Southern USA
    Posts
    70
    A player can have multiple classes and a class can have multiple players, so you cannot put ClassID as a foreign key in tblPlayers. Foreign keys are for one-to-many's, but this is a many-to-many. You need a junction table. Best I can tell, this additional table will have 3 fields (assuming you fix the primary key problem in tblPlayers):
    PlayerID
    ClassID
    Level
    Relate this table to both tblClasses and tblPlayers.

    Also, you have way too many fields participating in the primary key in tblPlayers, and you must change it. PlayerID should be unique and will suffice. If you need to ensure that no one has the same combination of first and last name, or the same combination of Char firstname and Char last name, use compound indexes for that.

    If you leave it as it is, you will have a nightmare to manage, and a lot of unnecessary redundancy.

  3. #3
    Dalagrath is offline Only a Man
    Windows 7 Access 2007
    Join Date
    Oct 2010
    Location
    Colorado
    Posts
    45
    I am not quite understanding what your Junction table means and how it supports what I am going for. And thanks for the tips about the Primary Keys in tblPlayers.

    What I am going for has many variables attached. The classes (if not multiclass) have around 3 or 4 variables and those variables have up to 20 variables depending on there level.

    Here is an example of what I am trying to put into a database. (This is one class of around 30).



    And here is the multiclass shpeel:


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

Similar Threads

  1. Multiple foreign key in table
    By pmstirling in forum Database Design
    Replies: 10
    Last Post: 02-23-2010, 04:00 PM
  2. import the foreign key into the primary key
    By vCallNSPF in forum Forms
    Replies: 3
    Last Post: 01-14-2010, 06:51 PM
  3. How to query using a foreign-keyed name
    By david28 in forum Queries
    Replies: 0
    Last Post: 10-17-2009, 12:57 PM
  4. Getting a foreign key set
    By bkelly in forum Access
    Replies: 5
    Last Post: 08-18-2009, 09:22 PM
  5. Foreign Data
    By bmiller in forum Queries
    Replies: 0
    Last Post: 03-21-2006, 01:02 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