how do I handle a changing lookup table?

[jrg24]

I am accessing data from our sales system to do some in depth analysis. This is done in a separate application. I export information from the system and upload into access in csv format. Part of this process is to lookup a vendor ID from a table of vendors and return a specific location that the vendor services.



My issue is that the location field in the vendor table is apt to change over time. This is a human data entry issue that is not supposed to happen but alas it does. A new record is supposed to be created for every change, but in practice that is not what happens all of the time. An example would be that when I go to my lookup table in January i see that vendor ABC services Dallas. When I look in February they service Houston. Houston is correct, but only for the period of February and forward until the value changes again. Likewise, Dallas is correct for January.

This would not be an issue if I was only at this data for each month, but I have to create this analysis year to date every month because we have no good cutoff in our sales system and invoices from prior periods can be added or changed.

What would be the best way to ensure that all transactions involving ABC for January show Dallas as the service location and February show Houston? My thought is to create a new vendor lookup table for each month and create a query that references the invoice month with the appropriate vendor table. Is this the best way to do this? Is there another way you would handle this issue? Below is an example of my vendor lookup table. Any input would be appreciated

Vendor ID	Vendor Name	Date Started	Date Ended	Servicing location	Vendor Type
300000610	Bob Smith	1/1/2010	9/12/2014	ATL	CF-DR/D10

[June7]

How does having a separate table for each month resolve data entry errors? This just complicates db design. Any process that requires design modifications as a routine is not optimized. The best approach is effective data quality control. There is something wrong with the business process that should be addressed.

[jrg24]

How does having a separate table for each month resolve data entry errors? This just complicates db design. Any process that requires design modifications as a routine is not optimized. The best approach is effective data quality control. There is something wrong with the business process that should be addressed.

I am aware. I have been preaching the same thing you are saying for some time and things are just now beginning to be addressed. Management changes, antiquated sales system not suited for an enterprise of our size, fast growing new company not prepared for growth, etc. The list goes on but for now it's what I have to work with.

So I assume you are saying there is no good way to do what I am looking to do? If there really is no good way then I will let the records update as they may and tell everyone that they just have to deal with the ramifications of these changes. I've done it before and I have no problem doing it again. Garbage in, Garbage out.

[orange]

It would seem that a Company may service more than 1 location. And that you may be trying to constrain the data to make it more convenient for your processing. The underlying issue may be structure, along with some undisciplined data entry.

Just an observation, but you know your business better than the readers do.

[ssanfu]

I see 3 options:

1) Employees need remedial training. This training is like training a mule/jackass. The first step is to hit them between their eyes to get their attention. Then continue with the training. NOTE: This type of training is usually frowned upon in the HR Dept circle.

2) Allow only additions to the look up table (no edits or deletes). You are using a form....right??

3) Set up an audit trail for the look up table. Employees tend not to care if there is no accountability. Track the changes and, at minimum, which computer was used to make the change. The user ID and computer would be best.
See: http://www.allenbrowne.com/AppAudit.html





Best of luck....

[Rawb]

You would need to know exactly when the data changed, and exactly what the data was before it changed. You would also need to store a timestamp for EVERY transaction you're looking at. It's a very complex solution and requires you to know a lot of data that you may not have any way of knowing.

Assuming you have all of that data, you can resolve the issue by adding a RevisionDate Date/Time Column to your Table and making it part of the Primary Key. Then, during your import, you compare each Record being imported to what's already in your db. If any of the data is changed, you create a new Record with the appropriate RevisionDate.

When you're looking up data, you just make sure your Query is looking for the Record with latest RevisionDate that's on or before the transaction date.

Even though it may get some users in trouble, you may be better off explaining that new Records are required so that you can track changes like this and when a user simply updates an existing Record "because it's easier," they're damaging the integrity of the system and making it virtually impossible for you to generate correct Reports.

[jrg24]

The example I provided was overly simplistic. These are really vendors and employees that deliver freight around the country. Per regulations these guys have to be domiciled somewhere so if someone relocates to a different city for instance, they change domiciles. Of course new record should be created when this happens - Our new compliance manager is actively working and training to make sure this happens, but some things are still missed. Data integrity is still an issue for us, but I can say that we are certainly better than we once were. The problem I describe is not a widespread problem, in fact it is quite rare, maybe once every other month or so and only for one vendor at a time, but it does happen and can make a difference to users of my reports. From the birds eye view it will not matter but the managers in charge of our locations do care because it affects the way costs are allocated to them on their P&L. Also, I am the only user of this database. No one else will be updating records in it.


SSANFU:
1. agreed
2. unfortunately the source data is outside of my realm of authority. I am in the finance group and that responsibility lies with the compliance manager. I have had conversations with his employees about how they enter data and had followup conversations with him where he echoed that any material change to a record means that a new record must be created... We shall see how it goes. I have not seen any changes recently but I know they have happened in the past.
3. We do have an audit trail but unfortunately AFAIK the only way to see it is to go into each record in our sales system individually to see who made what changes.

Rawb:
I agree with you. Like I said my example was overly simplistic. These changes typically happen in the course of a month so a I would need to know both the exact day of the change and what the data was before it changed. After thinking about it for some time I think I will manually create the vendor lookup table. I can them compare against the most recent file form the sales system to see which, if any, records changed during the period. I expect these changes to be minimal. I will then have to find a way to update the appropriate records. I may try to incorporate a revision date into this process. Thanks for the idea. In fact I may use these occurrences to insist on a new record being set up and the related financial records be updated to reflect this new vendor ID...