updates...preventing changes to previously entered values

[Guest]

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?

 

[Jeff Boyce]

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

 

[ajetrumpet]

You didn't include the requestor field in your update query did you??
You should only have one field (department) in your query and both strings for the "Update To" line and the string to be updated in your query grid. Your relationships shouldn't matter, not as far as I know anyway...

 

[Spurious Response]

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.

 

[Guest]

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).

 

[Guest]

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?

 

[Jeff Boyce]

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

 

[Guest]

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).

 

[Jeff Boyce]

So, how does this relate to your original post about losing data?

Regards

Jeff Boyce
Microsoft Office/Access MVP