Help Setting Up a History Table

[EternalMyrtle]

Hello,

I need help setting up a history table for contacts and the companies that they are associated with. I am sure this will be obvious to some of you database veterans but I am fairly new to Access and I can't seem to figure out the best way to accomplish what I am trying to do.

Here is what I need to do:

When a contact's employment status changes, I need to change the contact's current company association but somehow maintain his or her association with the previous company so that s/he can still be associated with past projects.

So, in my contacts table (TBLContacts), I have a foreign key field "CompanyFK" that links to my companies table (TBLCompaniesPK). There is a one to many relationship between TBLCompanies and TBLContacts.

I want the CompanyFK field to be the current company but somehow link the person with past companies too so that the project directories and subforms will continue to show the contact's association with the parent company.

Does this make any sense? Maybe I don't need a history table but something else?

I really appreciate any thoughts on this. It has been eluding me for some time. I have a similar problem with companies that change name, too. How to deal with takeovers, name changes, mergers, etc.

Thank you!

 

[catalin.petrut]

Yes, it makes sense, but you need to redesign your relationships. You will need a 3th table, a junction table. So, you will have tblCompany, tblContacts and tblStatusHistory (or any other name you want). On the tblStatusHistory you will have tblContactsFK, tblCompanyFK, dateofstatuschange, status. This way you can keep history for each contact or for each company.

 

[EternalMyrtle]

Hi, thanks for your reply

Do I still keep the CompanyFK field in tblContacts or do I just use the junction table?

If I am still keeping the CompanyFK field in tblContacts, how do I create a lookup in my forms to both tables?

I have a lot of cascading combos on my forms to filter the contacts that will appear when a company is selected. It works seamlessly with the single CompanyFK field. I worry that if I use the junction table solution, my lists will be populated with a lot of outdated data.

Does this make sense? Sorry it is hard to describe...

 

[catalin.petrut]

You will have to use only the junction table. Try doing it and after that you will see the logic for the forms.
PS: use the thanks button if you feel like it!

 

[jdraw]

Here's a youtube on Many to Many that you may find helpful.
https://www.youtube.com/watch?v=JBG4SzNhA9A

 

[EternalMyrtle]

You will have to use only the junction table. Try doing it and after that you will see the logic for the forms.
PS: use the thanks button if you feel like it!

So, I set up my junction table but the logic for the forms is still not very clear. My problem does not have to do with a lack of understanding about junction tables or many to many relationships but how to effectively manage how this particular data should be presented to the user

I use a lot of junction tables and have not had the same problems with them but the contacts are like a moving target--changing jobs, dying, etc. I don't know how to deal with these issues.

I guess I have to find a way to articulate my specific problems and head over to the forms section.