Accounting for change in data over time

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 


0
Larry
12/6/2007 6:03:55 PM
access 16762 articles. 2 followers. Follow

2 Replies
243 Views

Similar Articles

[PageSpeed] 52

On Thu, 06 Dec 2007 18:03:55 GMT, "Larry Kahm"
<lkahm@nospam_heliotropicsystems.com> wrote:

>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]
0
John
12/6/2007 10:17:18 PM
Thanks John!

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

Larry

"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message 
news:svsgl3l6o1kklcgp0qnu4k305m5iiqfmaq@4ax.com...
> On Thu, 06 Dec 2007 18:03:55 GMT, "Larry Kahm"
> <lkahm@nospam_heliotropicsystems.com> wrote:
>
>>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] 


0
Larry
12/7/2007 12:15:59 AM
Reply:

Similar Artilces:

Charting Data
Using Excel 2003 I have data that I want to chart, but I'm really not sure where to start. Is anyone willing to look at my Excel file and tell me how to chart this data? It would just be easier for someone to look at it. ANY help would be greatly appreciated, Karen Happy to have a look tomorrow. Please get my email address from my website -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Karen" <Karen@discussions.microsoft.com> wrote in message news:8ABB289D-92EB-4344-9DB9-09E6293041AD@microsoft.com... > Using Excel ...

Loading data from CArchive
I've written the following function for my program. Seems to work fine, however the only problem is I don't know what parameter to put in the while loop, as you can see at the moment I just have '1' which will obviously create an infinite loop, to know when to stop pulling data from 'ar'. void CLogicSimulatorDoc::Serialize(CArchive& ar) { int posx, posy, input0x, input0y, input1x, input1y; int type; if (ar.IsStoring()) { // TODO: add storing code here gate * gLoop; for (gLoop = gFirst; gLoop != 0; gLoop = gLoop->gNext) { input0x = (gLoop->i...

Lead time in a workflow
We are trying to add a field that would allow a user to enter number of days that would then be a conditional statement in a workflow. Would anybody have any idea what attribute type we should use and the basic workflow steps and type? Old School Hello Old School, You should use integer attribute type and you just click on settings on the left nav bar on Microsoft CRM and click on Workflow on the left navbar , create new workflow and select the type of entity you want to use and after that the workflow designer window will open to you so you can add a conditional branch and in the...

Apointment time Changes
I have a group of users that when they send apointments or receive apointments they time changes by an hour.. for example they send me a 3:00PM apointment ill get it for a 4:pm apointment.. they are on the same network as i am.. same exchange server... no one else has this problem.. has anyone come across this problem?.. any help would be great! Thanks Chris The settings to adjust for daylight savings time are in Windows as well in Outlook Calendar options. Both need to be checked. -- Bharat Suneja MCSE, MCT -------------------------------- "Chris" <Chris@discussions.m...

Can't create an account to account relationship in the account ent
Is this because the default (Parent Account) relationship already exists? Can you not have duplicate relationships? Thanks in advance for the clarification. > Is this because the default (Parent Account) relationship already exists? Yes. > Can you not have duplicate relationships? Yes, you cannot have duplicate relationships in this manner. To do this you need to use the Relationships/Relationship Roles functionality, where you can pretty much do any kind of relationship between clients (ie accounts and contacts.) HTH, R. "GoodByeGoldmine" wrote: > Is this beca...

exchange accounts will not delete from site
I have recently recovered one of my exchange 5.5 servers that crashed. Mailboxes have been restored and replication appears to complete only down to the restored server but not up to the main site mail server that holds the global address list. Due to lack of information in backups, it was necessary for me to manually recreate all mailboxes and then restore mailboxes. However, the new accounts have created with a "2" appended to each mailbox name. Example john@us.com is automatically created as john2@us.com. How do I purge the old accounts from the global list so that I can crea...

Automatically Change Lead Status????
I am new to this, but I was wondering if there was a way to change the lead status on numerous leads (275) at one time that were created on a specific date (imported in) but only if they still stay New. What we are doing is we have mailings going out each week where then our sales personnel have 30 days in which to contact the clients that received the letters. The sales personnel are then supposed to change the lead status from New to Contacted. If they have not contacted the clients I want to be able to run something that changes all of those statuses to overdue. Any help would be...

Muliple Exchange OMA User Accounts from One SmartPhone
I wish to be able to access multiple user accounts (all on the same Exchange 2003 server) from a single smartphone running Windows Mobile 5.0. After setting up OMA on Exchange and the smartphone and visiting https://FQDN/oma all works well after entering the username and password for one account, but then the smartphone remembers the login (presumably via a cookie) for future visits to the same URL. There seems to be no easy way to delete the cookie on the smartphone, so all subsequent accesses to OMA go straight through to the same user account. One way I have considered tackling this is to ...

change the color of points in 3D surface chart using VBA
I wanna change the color of single points in a 3d surface chart according to a seperate value for each point (i.e. date, time, price chart with the surface color representing the volume). I figured it out for 3d column charts using the sereiesCollection(i).points method but found no solution for a surface chart. Any ideas ? Liontrader, For the 3d surface chart, you have to go to the legend key to change the colors. Try something like this: ActiveChart.Legend.LegendEntries(1).LegendKey.Interior.ColorIndex = 6 ActiveChart.Legend.LegendEntries(2).LegendKey.Interior.ColorIndex = 3 ---- ...

Data Validation Dependant Lists
Hi there, Is it possible to 'source' data validation/dependant lists from seperate worksheet within the same workbook. I know it is possibl when using only data validation lists, but when I try to sourc dependant lists using '=INDIRECT(Sheet2!A2) for example, Excel say this is not allowed?? Kind regards And -- Message posted from http://www.ExcelForum.com If you give that range a name, you can use it in the Data|validation dialog. Debra Dalgleish explains it at: http://www.contextures.com/xlDataVal01.html#Name "andyp161 <" wrote: > > Hi there, > ...

Extreme Delays Deleting Change Orders from Project Accounting
We use Project Accounting for Dynamix GP and we are attempting to start using the Change Order features to track changes made to project budget and fees. Unfortunately, during testing, we are experiencing a five to ten minute delay whenever we try to delete an incorrectly entered change order. This occurs even if we simply start to create a change order, never use it for anything and immediately attempt to delete it. Has anyone experienced this, or can anyone propose any clues on what I should look at to find what is causing this large pause? -- Jim Rowley Business Analyst II ...

how to make gaps in plotted data when cell has formula
I have a simple data set with a value for each month except June. I want the chart to reflect a gap for June, not a zero value. Although I did tools, options, chart, make empty cells plot as gaps, it still plots the June value as a zero. In an attempt to get rid of div/o error, I have a formula in the data set as If (b5=0, "", c5/b5) . What I'm plotting is the quotient c/b. We have determined that the formula is what is causing the problem. Interestingly, if I open this same worksheet (as is) in quattro pro, the gap appears in the chart. Is there a different way to do th...

pivot table changes when data is refreshed.
Hi, I have a pivot table with grouping based on a field called "period which is of date type. I have grouped it into months and quarters However when i refresh the pivot table whenever the data (which is in seperate sheet) is changed, the groupings are going out. I want t preserve the groupings as they are like a template. How do i stop th changing the format and layout of the pivot table whenever i refres the data? any ideas? please help. regards Kiran:mad ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages d...

Re Label Accounts Field
Anybody know how to change the Accounts field label to something else e.g. Organization, or Business? You can rename the 'account' entity to be 'Business'. Organization is a reserved word in CRM (there is already an orgainzation object) so it's off-limits. Dave "Owais" <Owais @discussions.microsoft.com> wrote in message news:25767F85-513C-40F7-A82F-F0E5A627D9FF@microsoft.com... > Anybody know how to change the Accounts field label to something else e.g. > Organization, or Business? ...

Total from SubReport Data But Not in SubReport
Hello, This is probably pretty simple, but I have created a report where the main display is a subreport of items ordered by the customer order and price. I would like to total the prices in the main report, but not from a total on the subreport. The price field is called Expr5. I have tried =Sum([Expr5]), but this gives and #Error I can't figure out what you are asking. If you don't want the total from the subreport, what do you want? Values displayed in a report must come from somewhere. "Expr5" means nothing to any of us other than you haven't taken the time ...

want to transfer out funds from a fund within an account to a fund within the same account.
I want to do this to preserve my cost basis. I have a 401k that lets say has a fund that I bought for 100 bucks and it gained 10 dollars. when I issue a buy/sell to transfer it to another fund (within the same 401k account) the new cost basis is 110 dollars instead of the 100 that I actually spent. I know that using transfer in/out will preserve this cost basis, but it doesnt seem to work within an account per other threads of this nature. How can I do something similar within the same account? I am using 2005 premium. Thanks Vic. I don't think your use of Cost Basis reflects tax la...

Forwarding email to an external account
If I want to foreward email coming into my exchange account to a personal account such as hotmail for example, how can this be done? In Active Directory Users and Computer, you look at the User's Properties, you look under the "Exchange General" Tab, you look at the "delivery option", you see "forwarding address", you say "ha, ha!" and do some jiggy dance :) I only do this for ex-employees for 30 days at the request of HR. -- Sincerely, D�j� Ak�m�l�f�, MCSE MCSA MCP+I www.akomolafe.com www.iyaburo.com Do you now realize that Today is the Tomorro...

Problem configuring Outlook 2007 w/ job email account
I recently installed Outlook 2007 to my laptop (which uses Vista). Upon trying to "setup" Outlook a small box appeared in the upper left corner of the screen which stated "configuring outlook accounts." A box appeared asking for "Microsoft Exchange Server" and "mailbox." I was not sure as to what to enter so I entered "dss.sc.gov" (which is everything listed after the "@" in my job email address) for the server and my name for mailbox. I then received the following error message: "The connection to Microsoft Exchange is ...

Loan account
Hi, Something else that's giving me trouble. I've set up a loan account - all seems to be fine. It's a three year loan, and the first two payments had already been made when I set the thing up in Money. Money coped with this fine, and within the register of the loan account it now accurately shows the first two payments and the principal-element of each payment, together with the diminishing value of the outstanding loan. However, it doesn't seem to be linked in any way to the account from which the payments are actually being made. Conceptually it seems to me that the loan ...

Can't set up HTTP account!!
I'm setting up Outlook on a friends computer. On their old computer they were able to view their Charter Communications email account through Outlook. I am trying to set it up so they can do it in XP, but it won't let me see their messages it just comes up blank. What am I doing wrong??!! P.S.- They don't have an account through Outlook/Microsoft Thanks, Locke ...

Online Services
With many banks now changing over to this Site Key verification during login, Money no longer is able to access those sites during it's automatic updates and statement downloads. Does anyone know how long it takes for the programmers to make fixes when a bank does that? I haven't been able to access my TCF Bank info from Money for a couple weeks now since TCF changed to the Site Key. ...

Multiple Email Accounts
I have three email accounts. I can only send message from one - some sort of automatic default. How can I select (with each email), what account I want to use to send a message? What version of Outlook are you using, and if 98 or 2000, what mail support mode? If you're not sure, look at the second line of Help | About Microsoft Outlook -- it should say "Internet Mail Only" or "Corporate/Workgroup". (Outlook 97, 2002, and 2003 don't have separate modes.) -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please ...

Convert Lead > Account-Contact-Opportunity Error
Hi, I have several leads opened. If I create a new one it will work "but" especially with this ONE lead I don't get it, I convert it either to Account - Contacts or Opportunity and gives me the following error. I repeat this is only for this one. I tried to close reopen change settings etc... Still not working. Microsoft CRM Platform Error Report: Error Description: Exception from HRESULT: 0x80042F09. Error Details: Details on this error have not been provided by the platform. Error Number: 0x80042F09 Source File: Not available Line Number: Not available Date: 10-2...

Change HTML in fly
Hi everyone! In my project I use CDHtmlView class (http://www.codeproject.com/dotnet/dhtmldialog.asp) But I can't change html For example: <html> <body> <body> <table> <tr><td>asdf</td></tr> <div id=to_change></div> </table> </html> when I try to change elements in <div> by "id" SetElementHtml("to_change", tbstr), tbstr="<tr><td>qwer</td></tr>" and after that a verify data: tbstr=GetHtmlElement("to_change"); tbsr == "qwer</td></tr&g...

Cant change FirstPageTray of Word Document
I am getting below error while assinging the Printer Tray. System.Runtime.InteropServices.COMException (0x800A1200): Value out of range at crosoft.Office.Interop.Word.PageSetup.set_FirstPageTray(WdPaperTray prop) here is the code I wrote. With WordApp.ActiveDocument.PageSetup .FirstPageTray = 261 .OtherPagesTray = 261 End With I am using Office Word Interop in my win application. Thanks, Irfan Shaikh You may find some information at http://pubs.logicalexpressions.com/pub0009/LPMArticle.asp?ID=183 that will help. ...