Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I have a table [tblEmployee] that maintains employee names and data. The Employee Name info feeds several different forms, and within the forms there are multiple records. The employee name is selected through a drop-down box (on each various form) that feeds from the above mentioned table. So consequently the employees name is captured in several different tables based on the selections in the related forms.

    Everything works great until Mary Jones gets married and becomes Mary Smith. The drop-down boxes all reflect the name change...but when I want to pull back all of the related records for Mary Smith in a report, I only get the records entered after the name change was made; all previous records are still shown as Mary Jones.

    The databases I designed are not accessible to me once I am done with them, short of having someone email it back to me for any changes. We try to keep emailing db's back and forth to a minimum. I am trying to develop a system where the user can update/change the underlying name change in all related tables through the use of an update form (???) I would prefer to make this as "stupid proof" as possible, so any requirement for developing an update query, accessing the design window, etc. is not an option.

    I appreciate any suggestions, or examples that are submitted.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    [quote name='SmokeEater' post='789786' date='19-Aug-2009 11:01']Everything works great until Mary Jones gets married and becomes Mary Smith. The drop-down boxes all reflect the name change...but when I want to pull back all of the related records for Mary Smith in a report, I only get the records entered after the name change was made; all previous records are still shown as Mary Jones.
    I appreciate any suggestions, or examples that are submitted.[/quote]
    It appears that your combo boxes are actually storing the name in the related records, rather than using a numeric index to point to the Employees table. To change that is probably a major redesign as the tables would also need to be changed - the path of least resistance would probably be to add a long integer index to each table where the names are being stored, and use the combo box to use that value as the bound data object. I'll see if I can point out an example that is fairly simple, but it may take a while.
    Wendell

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    OK - you don't indicate which version of Access you are using, but if you are in 2003 or older, the Orders form in the Northwind sample database is a good example. Orders are taken by a salesperson, and the ID number for the salesperson is store din the Orders record, rather than the name of the employee. Your situation should be able to use the same concept - the trick is a combo box that uses two columns and displays the Person Name but saves the Person ID.
    Wendell

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='SmokeEater' post='789786' date='20-Aug-2009 04:01']I have a table [tblEmployee] that maintains employee names and data. The Employee Name info feeds several different forms, and within the forms there are multiple records. The employee name is selected through a drop-down box (on each various form) that feeds from the above mentioned table. So consequently the employees name is captured in several different tables based on the selections in the related forms.

    Everything works great until Mary Jones gets married and becomes Mary Smith. The drop-down boxes all reflect the name change...but when I want to pull back all of the related records for Mary Smith in a report, I only get the records entered after the name change was made; all previous records are still shown as Mary Jones.

    The databases I designed are not accessible to me once I am done with them, short of having someone email it back to me for any changes. We try to keep emailing db's back and forth to a minimum. I am trying to develop a system where the user can update/change the underlying name change in all related tables through the use of an update form (???) I would prefer to make this as "stupid proof" as possible, so any requirement for developing an update query, accessing the design window, etc. is not an option.

    I appreciate any suggestions, or examples that are submitted.[/quote]
    Are your related tables related on the person's name?
    If so you could setup referential integrity on those tables with cascade updating, so when you change the name ion the master table the name change filters down to the related tables.

    However, i agree with Wendel, you should setup a Autonumber as the personID then use that to relate tables, then you just need to change the persons name once only.

  5. #5
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Wendel and Patt,

    Thanks both. I understand what you are saying and will make changes to the db. It is still in the development stage and now is the best time to make this kind of change. Thanks again for your replies.

Posting Permissions

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