 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / July 2007updates...preventing changes to previously entered values
im an access newbie and im amazed at how complicated creating a database can be! is there a way to prevent updates to previously entered values? for example i have a table that lists departments and their associated regions, and another table (linked to the former) that lists a requestor's name and the department they belong to. this table is linked to a main table that houses all info pertaining to a specific request. my problem is that if i update the department that a requestor belongs to, the main table erases the requestor name, and i am faced with having to select another requestor from the original requestor's original department. what i am aiming for is to be able to leave the original entries the same...for example yesterday joe smith worked in the IT department and submitted a request. today joe smith was promoted and now works in human resources. i go to the requestor table and update the department associated with joe smith from IT to human resources. the request entered yesterday now shows IT as the requesting department, but joe smith's name is missing (since he no longer belongs to IT and the IT table drives the options available for the requestor field). how do ensure that all of joe smith's requests prior to today list him as part of IT, and all of joe smith's requests today forward list him as part of human resources? Everything in Access starts with the data. Post back a description of the table structure you are using. For example: tblPerson PersonID FirstName LastName ... tblCourse CourseID CourseTitle .... trelRegistration RegistrationID PersonID CourseID RegistrationDate ... Regards Jeff Boyce Microsoft Office/Access MVP > im an access newbie and im amazed at how complicated creating a database > can [quoted text clipped - 32 lines] > of > human resources? I would add "department" fields to the data table that stores the request data as having been a lookup at the time of the request. Then, a query examining old request data would show the department data in the field stored in that table, and NOT perform a lookup to the current department field in the "joe smith" entry. In other words, perform the lookup into the department table (or wherever that data resides) and then KEEP that data in the request table, stored as data that was extracted at the time of the request. Sure it adds a bit of data to the store info, but it is time centric and corrects the problem. So a lookup of an old request does not query this department table for the department info, it KEEPS said data at the time the request was made, and even if Joe's department assignment changes, that old request data would not. So, stop performing a "current department" lookup in your query, and ADD that field to the request data table so that it becomes a unique element of that request data. One could even add a field that shows what "joe's" current department is, if contact with him is needed or whatever. Hell, if the guy (joe smith) leaves the company, your request info query on old request data would have no entry in such query returns, so you actually NEED to do it the way I described. Make the saved data smaller by using department codes, and a lookup to the larger department description text. >Everything in Access starts with the data. Post back a description of the >table structure you are using. [quoted text clipped - 60 lines] >> of >> human resources? The tables are as follows: tblRegion RegionID RegionName tblDepartment DepartmentID DepartmentName RegionID (link to tblRegion) tblRequestor RequestorID RequestorName DepartmentID (link to tblDepartment) tblRequestInfo this table has fields for information specific to each request, which include region, department, and requestor fields that are limited to choices in the tables mentioned above using cascading combo boxes. for example, it has a "Requestor" field that offers options based on a query on what was selected for Department. The source data for the options comes directly from the tables mentioned above (tblRegion, tblDepartment, & tblRequestor). > Everything in Access starts with the data. Post back a description of the > table structure you are using. [quoted text clipped - 60 lines] > > of > > human resources? would changing the data entry property from no to yes in the form properties solve for this problem? i gave it a shot and it seems to keep the original data intact. or, will this lead to unforseen problems down the road? > The tables are as follows: > [quoted text clipped - 84 lines] > > > of > > > human resources? I'm having trouble reconciling your original post about losing data in a "main table" with the table structure you posted. How have you connected which forms to which tables (?using queries?)? Regards Jeff Boyce Microsoft Office/Access MVP > would changing the data entry property from no to yes in the form > properties [quoted text clipped - 105 lines] >> > > of >> > > human resources? tblRequestInfo holds all of the information input in the form. the region field in the form is limited to the options in tblRegion. the department field then uses a select query to filter the departments (from tblDepartment) available for selection (making only those that belong to the selected region available). finally, the requestor field in the form uses a select query to filter the requestors available for selection (from tblRequestor). > I'm having trouble reconciling your original post about losing data in a > "main table" with the table structure you posted. [quoted text clipped - 115 lines] > >> > > of > >> > > human resources? So, how does this relate to your original post about losing data? Regards Jeff Boyce Microsoft Office/Access MVP > tblRequestInfo holds all of the information input in the form. the region > field in the form is limited to the options in tblRegion. the department [quoted text clipped - 137 lines] >> >> > > of >> >> > > human resources? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.