Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2004
    Location
    NY
    Posts
    113

    Unanswered: 1 table accessing many fields

    Is this bad design,

    If I have a main table and an employee table. The fields in the main table, will acces the employee 3x but seperatly. For example, in the main table it would have fields, initiated by, completed by, and approved by. There is no reason to create a seperate table if all I will be doing is accessing names from a combo box. Is it okay to do this?
    thanks
    ======================
    Levent
    Access 3

  2. #2
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    You can certainly use hardwired names in three separate combo boxes but this means that you would have to edit the source list every time a name was added or deleted for each of them. The simplest way would be to have a table of names with three boolean (Yes/No) fields indicating whether the name had the authority to initiate, sign off and approve the activity. Then you could either have a separate query or an SQL statement in each combo box to capture complying records. That way you don't have multiple entries for names with multiple authorities.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you will not be able to use a bound combo box to do all three tasks. so your options are either 3 separate combo boxes or a single unbound box.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Aug 2004
    Location
    NY
    Posts
    113
    Thank you for your response,

    What was happening was that I had the tables joined but access allows only 1 join per table(s) with RI. I unjoined the tables and use the control source for all three combo boxes to tblEmp.
    ======================
    Levent
    Access 3

  5. #5
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I am a little confused, but I think you might be able to do what you want. In Relationships, did you know you can have more than one copy of the same table? Access names them differently (_1, _2, etc.), when enforcing referential integrity add a copy of the table for each relationship you want to create. In your case, add three copies of the Employee table and link each field to a separate copy of the employee table. Let me know if I missed the boat.

  6. #6
    Join Date
    Aug 2004
    Location
    NY
    Posts
    113
    Interesting, I know you can do that but I have never tried that. What would happen if I were to add new employees into the table? Would I have to add it to all 3? What I did was I removed the relationship and controled sourced tbl employees for all three combo box.

    It seems to give the result that I want, in a RI I don't know if this is correct or if it will lead to errors down the road?
    ======================
    Levent
    Access 3

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    As DCkunkle says you can have multiple realtionships between the same tables.

    all they are is Relationships.

    all that has changed is that you have added another reference, to the effect that main table must have a valid employee ID in column X (where valid is either null OR exists in tblEmployee)

    you dont' have 3 separate tables, merely 3 separate rules that say check any value in this column in main table to make sure that they exists in the employee table

    Its a perferctly valid table design

    however it doesn't get round your existing problem which is that if you go down this design you cannot use one bound combo box to service all three columns in you main table.

    depending on you familarity with Access & VBA, you could use one unbound combo box to select employees and move it to one of the three destination columns - its a bit of fiddly VBA programming that begineers may not want to handle. the alternative is 3 separate combo boxes (which is easy to do, not the most elegant or low resource usage).
    HTH
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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