Accounting for change in data over time

[Larry Kahm]

Please help, I'm having a brain freeze today, and I can't figure out what
should be obvious.

I have three tables:

tblOrgs that holds information about an organization
tblContacts that holds information about the peple that work at
organizations
tblMarketingRecords that hold marketing information about the organizations
and the contacts that have been made.

I need to account for two things that can change over time: the first is
the person's title; the second is their association with the organizaion.

In the first case, a person's title was Admin and is now Associate. If any
tblMarketingRecords records were created when this person was an Admin, I
need those older records to retain that information and not pick up the
current title - but I still need to see the current records with the current
title.

In the second case, a person could retire from an organization - or move
from one to another. If any tblMarketingRecords records were created when
this person was at FirmA, I need to retain that in the older records, as
well as pick up the fact that he may be working at FirmB.

What am I missing about the links among the tables?

Thanks!

Larry

 

[John W. Vinson]

Please help, I'm having a brain freeze today, and I can't figure out what
should be obvious.

I have three tables:

tblOrgs that holds information about an organization
tblContacts that holds information about the peple that work at
organizations
tblMarketingRecords that hold marketing information about the organizations
and the contacts that have been made.

I need to account for two things that can change over time: the first is
the person's title; the second is their association with the organizaion.

In the first case, a person's title was Admin and is now Associate. If any
tblMarketingRecords records were created when this person was an Admin, I
need those older records to retain that information and not pick up the
current title - but I still need to see the current records with the current
title.

In the second case, a person could retire from an organization - or move
from one to another. If any tblMarketingRecords records were created when
this person was at FirmA, I need to retain that in the older records, as
well as pick up the fact that he may be working at FirmB.

What am I missing about the links among the tables?

Probably nothing.

What's missing is some fields in the tblMarketingRecords table. If you're
linking to tblContacts to find the name or job title of the person, you'll
find whatever is *in* that table - that is, the current job title or the
current person there.

That information may be different from what that person's job title was *at
the time of the contact*.

It may seem redundant to have a jobtitle field in both the Contacts table and
the MarketingRecords table, but it really isn't - those are two separate
pieces of information, the person's title today and the person's title as of
June 18, 2003.

You'll probably need code in the data entry form to *copy* the
information-as-of-today from tblContacts or tblOrgs into tblMarketingRecords,
but you do need to store that information!

John W. Vinson [MVP]

 

[Larry Kahm]

Thanks John!

Duplicate the required historical fields in the appropriate table. That was
certainly a "smack my head and say, D'uh!" moment.

Larry