maintaining historical records

  • Thread starter Thread starter B
  • Start date Start date
B

B

Can anyone help me with maintaining historical records in
the database.
Basically I have a vendor table, which is linked to the
transaction table. Sometimes the Vendor changes their
name, I want the current records to show the new name
however I also want to maintain the previous records with
the old name.

Any help will be appreciated.

Thanks
B
 
There are of course are many ways to do this. One way
would be to have a decimal Vendor ID #. The current
vendor is, for example, 10 and older vendors are
10.1,10.2, etc. All history would roll up to Vednor 10.
You could then look up all transaction under the current
or original vendor name!
 
B said:
Can anyone help me with maintaining historical records in
the database.
Basically I have a vendor table, which is linked to the
transaction table. Sometimes the Vendor changes their
name, I want the current records to show the new name
however I also want to maintain the previous records with
the old name.
Click to expand...

If there's no connection between data with the vendor's old
name and their new name, then what difference is there
between adding a new different vendor and adding a new name
for an old vendor??

If there is no difference, then just add an Yes/No field
named Inactive to the vendor table and set it to Yes for a
vendow that changes their name. Then add a new vendor to
new for future transactions. Any combo/list boxes that are
used to select a transaction's vendor can easily filter out
the inactive vendors so they're not used in any new
transactions.

If you have to maintain some kind of connection between
all(?) the various names that a vendor has used over the
years, then you should probably add a table of VenderIDs.
This way, you can add a VenderID field to the vendor name
table to use in a standard one(vendor)-to-many(names)
relationship.
 
Back
Top