1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Some Access questions

Discussion in 'Business Applications' started by Dragn73, Feb 14, 2007.

Thread Status:
Not open for further replies.
  1. Dragn73

    Dragn73 Thread Starter

    Joined:
    May 23, 2002
    Messages:
    29
    I will try to explain this clearly. I have a database that we will be using to track email or call in issues for our support team. i am having trouble with some of the requested modifications.

    1. I have a table called Manager Name with 2 fields. ID and Name. I imported this list from an excel spreadsheet and tied it to my form in the manner of a drop down box. Then the entered issue along with this field is stored in the issue table. When i enter my test record, it appears that in the issue table it stores the manager name, however if i change the ID that is associated with that name in the manager name table, then it changes the value in my issue table. This means that its actually tied to the ID number right? I tried to delete the ID column and assigned the Manager Name field as the primary key with no duplicates allowed. However when i tried to open the Issue table, it brought up a prompt window asking me for the ID number with an option to click OK or Cancel. If I click on Ok without adding in any value, then it opens up but doesn't show me any name in the field, so it still seems tied to the ID number somewhere that I cannot locate. The problem i have with this, is if I need to update this table and the ID numbers change, then it will mess up all of my stored issues. Does anyone know where this parameter prompt is coming from or how i can avoid it?

    2. Same tables as above. Once I can get the above resolved, my next challenge is that if a manager leaves the company, i would like to be able to delete their name from this manager name table. Right now if i do this, then it deletes the name from my issue table. I am looking for a way to add/remove/update Managers in the manager name table and drop down list, but yet still maintain the integrity of the issues table that have already been closed.

    I hope this makes some sense. Thanks for any help you can offer.
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Dragn, yes it all makes sense, a zipped copy of the database with a couple of fictitous records would help a lot though.
    Lets take the second question first, have you looked at the "Relationships" set up between the 2 tables?
    You will probably find that the "Link" is a One to Many from the Managers Table to the Issues Table. If you click the Link and then Right click it to edit it's Properties you should find that "Referential Integrity" has been set, this means that the Issues table records can only have Managers entered in to it that exist in the Managers table. You should also find that "Cascade" record changes has been ticked including the "Delete Records in Related Tables".
    To do what you want Un-tick that one, now all the other cascades will work but when you delete a Manager his Issues will remain in the Issues Table.

    Back to your first Question, the way that the tables were set is the correct way, except you might be using a "Lookup" List for the Manager in the Issues Table.
    I personally do not like them at all, they are confusing when you look at the data in the table and are very difficult to work with in Queries, forms and reports.
    I prefer to use a Query and a Combo Box based on that query on a Form to make the Selections.
    So I would suggest that you leave the Managers Table as it was.
    The change of Managers name is also affected by the Refenetial Integrity Set-up. If the second Item is ticked all Changes to the Managers table will be "Cascaded" to the Issues table. Un-tick it to stop this happening
    What should happen is that the Administrator of the database should enter and maintain the Managers Data and Users should not be able change it, only "Select" it.
    The managers Names should not be changed other than to correct spelling mistakes and if a manager leaves either delete his record or have a "Deleted" checkbox that stops him being selected but preserves his name for viewing old records.

    I already have some databases that do this kind of "Logging", mostly for IT departments, so if you need any more help let me know.
     
  3. Dragn73

    Dragn73 Thread Starter

    Joined:
    May 23, 2002
    Messages:
    29
    Thanks OBP for the help, I didn't end up having any relationships, but I did go in like you suggested and found the lookups that were causing me some issues. Once I modifed these, it seems to work great. I have a checklist of modifications, so I am sure i will post again real soon.

    Thanks
    Dave
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/544133

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice