Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2005
    Posts
    87

    Unanswered: Prevent deleting linked fields

    I have a field in a table, with combobox to another table (rowsource to another table). When i delete some records in another table, it also deletes same records (that were already made) in primary table. How to make access leave all records that were made "by" another table?

    e.g. orders table has employee field (combobox of emplyees), and whenever i delete emplyee1 in employee table, it also deletes his name from order table (whole order stays, but emplyee field is empty).I want to remain this field in orders table after deleting emplyee from emplyees table...

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    instead of deleting the employee, consider having a column on the employee table which indictates if the employee is active / still employed. That way round the rest of your business logic holds. YOu will need to chaneg some of your form logic to reflect that - eg ex employees should not appear in combo box to select for new orders

    if the employee has left then orders with their name should not be able to have the employees name changed. In fact you may care to consoder if you shoul dimplement that feature on all orders (unless the person making hte change has some uber privilege)

    you could consider storing the employee name in the sub table - but this violates the normalisation rules. And in my view is not one of the "good" reasons to violate normalisation rules. If an employee changes their name, you would have to do an update to change their names in the order records.

    HTH

  3. #3
    Join Date
    Oct 2005
    Posts
    87
    Quote Originally Posted by healdem
    instead of deleting the employee, consider having a column on the employee table which indictates if the employee is active / still employed. That way round the rest of your business logic holds. YOu will need to chaneg some of your form logic to reflect that - eg ex employees should not appear in combo box to select for new orders

    if the employee has left then orders with their name should not be able to have the employees name changed. In fact you may care to consoder if you shoul dimplement that feature on all orders (unless the person making hte change has some uber privilege)

    you could consider storing the employee name in the sub table - but this violates the normalisation rules. And in my view is not one of the "good" reasons to violate normalisation rules. If an employee changes their name, you would have to do an update to change their names in the order records.

    HTH
    My problem was with relationships, i got rid of them and value stays now. But thnx for othet ideas, i'll think about changing something.

  4. #4
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Question

    Quote Originally Posted by YZF
    My problem was with relationships, i got rid of them and value stays now. But thnx for othet ideas, i'll think about changing something.
    YZF,

    Did you get rid of the relationships between your tables or just the Cascade Deletes/ Updates? If so (Cascade Deletes)you will have many orphaned records floating around with no way to make meaning of the data left. If you cut the relationships entirely how will that affect your database as a whole?

    BUD

  5. #5
    Join Date
    Oct 2005
    Posts
    87
    Quote Originally Posted by Bud
    YZF,

    Did you get rid of the relationships between your tables or just the Cascade Deletes/ Updates? If so (Cascade Deletes)you will have many orphaned records floating around with no way to make meaning of the data left. If you cut the relationships entirely how will that affect your database as a whole?

    BUD
    Actually i deleted all relationships, and started to build them again, but only where i need it. There are some tables that need onetime record only, and to stay there for the lifetime.So i don't need relationships there.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So how are you storing the employee information on the sales order

  7. #7
    Join Date
    Oct 2005
    Posts
    87
    Quote Originally Posted by healdem
    So how are you storing the employee information on the sales order

    Just type in (or select from combobox) employee name (which comes from employee table) and thats it.

  8. #8
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Quote Originally Posted by YZF
    Just type in (or select from combobox) employee name (which comes from employee table) and thats it.
    I guess if it works for you, but what is done in most cases is have a field like healdem suggests. What I have used was a CheckBox to reflect if this person or whatever is Active or not. There it would not reflect anywhere my programs logic didn't need it to show whether ComboBox or TextBox, etc. Then later on if you need that data it is still there, like old history records that you can later have a query to archive for you in another table. Orphaned records sometimes has unconnected data that isn't always so easy to find later. Then again you did re-design your relationships, so if it works for you, excellent.

    have a nice one,
    BUD

Posting Permissions

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