Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2011
    Location
    Currently in Kabul Afghanistan
    Posts
    47

    Unanswered: Tables and Look Ups

    I may be mixing apples and oranges but I need some assistance.

    I have a primary table called Employees that has a considerable amount of fields that depend on values from other tables to populate.
    I know that in one sense I can create a field called RegionID in the Employee Table and set it to number type.

    I have a table called Region that has 5 values in a text field.

    I have created a join table called tblJoinRegions that has three values: ID (autonumber) EmployeeID (number) RegionID (number)

    In the Relationships window I have dragged the primary key from Employee Table to EmployeeID in the tblJoinRegions. I have dragged the ID number from the Region Table to the RegionID field. I have enforced referential integrity. From Employee table to tblJoin I have a 1 to many From Region to Join a 1 to many.

    I saved the relationship. I open the Employee table in datasheet view and there is no "look up" relationship in the Region field.

    I know I can create a look up in a field in the Employees table in the design view, LookUp / Combobox /
    Code:
     SELECT Position.ID, Position.Position FROM [Position] ORDER BY Position.[Position];
    But I hear that this is bad design. Can someone help me clear the muddy waters? Thanks

    I have several primary goals. 1) to see values in the Employee tables from other tables. 2) Set the stage to be able to add and employee in a Form, 3) Set the criteria up based on values for calculated fields or an If Else statement (Employee contract Type = X) Then number of paid leaves = X etc etc etc...
    Last edited by jstpierre; 11-18-11 at 06:15. Reason: Additional Info

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    first off I don't think you need the autonumber column in tblJoinRegions
    defining a primary key of employeeid and regionid should be sufficient, and it will improve data integrity as you can no longer have duplicate employee and region definitions

    as to the rest of your problems Im nto sure I understand them, and tbh Im a little short on time today
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2011
    Location
    Currently in Kabul Afghanistan
    Posts
    47

    Table Clarification

    I mis stated. I am referring to the join table having a primary key in addition I have and EmployeeID and RegionID field.

    Does that help. Know you are busy. Thanks

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you do not need the autonumber column in tblJoinRegions
    why?
    its a spurious use of an autonumber column
    why?
    it doesn't add anything to the table
    defining the primary key as a composite of EmployeeID AND RegionID satisfies two requirements
    1) you have a unique means of identifying a row in your table
    2) you cannot have more than one definition (intersection) of Employee and Region. you can have multiple occurances of region, multiple occurances of employee, but you can only have one occurance of a specific employee and region combination. in your model the autonumber removes that constraint

    im not too sure what you are tryingto achieve, or where your problem lies.
    I have several primary goals. 1) to see values in the Employee tables from other tables. 2) Set the stage to be able to add and employee in a Form, 3) Set the criteria up based on values for calculated fields or an If Else statement (Employee contract Type = X) Then number of paid leaves = X etc etc etc...
    But I hear that this is bad design. Can someone help me clear the muddy waters?
    using the 'lookup' facility built in to Access table design is a bad idea. its OK if you now at design time that there is a closed set of options but otherwise its a ticking timebomb that will blow up in the developers face at some stage.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2011
    Location
    Currently in Kabul Afghanistan
    Posts
    47

    Table Clarification

    You sir....ARE THE MAN....I will have to chew on that last bit. I love it, to the point and clear.

    Just so I am clear, on my join table creation, remove the Primary Key and then set it to say EmployeeID (Number)
    RegionID (number)
    Then Join the Primary Key from Employee Table to EmployeeID in tblJoinRegion
    Then Drag the Regions Primary Key to the tblJoinRegions RegionID field also set to number.

    Does this set the stage for the correct intersection?

    Thanks again and I know you are busy..Jim

    Love the ref to the motorcycles! I have the Yamaha Roadliner 1865 cc cruiser.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •