Bound/Unbound lookup field

[Dawn]

We are creating a historical log that only records the values at that time of
transaction. I have a table with employee names and job titles that will
become a lookup for some child tables. So, with this, I'd like the lookup
field of job title to be bound to the child tables. However, later as job
titles change, I need the lookup table to be updated but I do not want past
data in the child tables to update with the new values. I need it to unbind.
There used to be a checkbox in Access 2002 to just enter in the "values"
from the lookup field. What do I do now in this case?
Thanks!

 

[Jeff Boyce]

Dawn

I'm not entirely clear on what you have or what you want...

It sounds like you want to have a way to have a "historical" record that
shows that "John Doe" was a "Line Worker" from "Date1" to "Date2", and that
the same person was a "Supervisor" from "Date3" to "Date4".

If so, you'd need one record that stored John's ID (you don't need/want to
store John's name redundantly), plus the RoleID, plus the relevant date
range values, and a second record that stores John's ID (same person, same
ID!), plus the (new) RoleID (different Role, different ID), plus the
relevant date range values for John in THAT role.

Without an idea of how your tables are structured, it's difficult to offer
any more specific suggestions...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Dawn]

Thanks so much for answering, Jeff.

I'll elaborate some more. I have a lookup table of employee names and job
titles (ie, Admin, Project Mgr...saying also that an Admin's next step is a
Project Mgr). Then a second table, say, with test scores. That child table
uses the employee's name and job title from the lookup and also records the
test score (and job title) at the time of testing. Let's say this is recorded
in QTR 1, and now it's QTR 4 and an Admin has progressed to a Proj Mgr. I
need to run a qry/report that will tell me test scores of the Admins. (i
still need that data that the employee took when he was an Admin)

Does that make any more sense?
Thanks again!

 

[Armen Stein]

Thanks so much for answering, Jeff.

I'll elaborate some more. I have a lookup table of employee names and job
titles (ie, Admin, Project Mgr...saying also that an Admin's next step is a
Project Mgr). Then a second table, say, with test scores. That child table
uses the employee's name and job title from the lookup and also records the
test score (and job title) at the time of testing. Let's say this is recorded
in QTR 1, and now it's QTR 4 and an Admin has progressed to a Proj Mgr. I
need to run a qry/report that will tell me test scores of the Admins. (i
still need that data that the employee took when he was an Admin)

When you record the test score, you need to record both the employee
key and their job title key in the results record. That way the title
is preserved "at the time" and you can report on that instead of the
employee's current job title.

This is an example of one of the reasons to "denormalize" a database
design - denormalizing for historical values.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Dawn]

That's an interesting way of looking at it. Linking two many-to-many
relationships with a conjunction table. I see. Thanks all, for all of your
help!