Tracking History Challenge

  • Thread starter Thread starter Sarah
  • Start date Start date
S

Sarah

I have access 2003 and have a table that I would track history - I'd like it
to go as follows:

On the table there is a list of stores and store managers
The stores do not change, but the managers in the stores change.
I would like for, every time there is a change in a manager, to add that
change as a new record in a separate file with the date of the change (or
start date of manager in store) - date should be input automatic - or can be
changed manually on first table.
And, if possible to add the timeframe for the last record.

For Example:
in Store 20 - Manager is input as Joe.
This creates a record in a separate table where store 20 / joe / 1.15.2007
then 2 weeks later, Manager for Store 20 is changed to Mike. This should
create a record in the separate table where store 20 / mike / 1.30.2007
If possible to add to previous record store 20 / joe / 1.15.2007 / 1.30.2007
(start/end dates)
Not sure if the end date part is possible but please let me know how to
setup the rest.

Thanks!
 
You need a one-to-many set of related tables like this --
Store --
StoreID - Autonumber - primary key
Name - text
Addr1 - text
Addr2 - text
City - text
State - text
Zip - text
Phone - text
Fax - text
etc. ---

StoreMGR --
StoreMgrID - Autonumber - primary key
StoreID - number - integer - foreign key related to store
Name - text
StartDate - DateTime - default =Date()
EndDate - DateTime
Home - text - Home phone
Pager - text
Cell - text
etc. ---

Create a relationship from Store StoreID to StoreMGR StoreID with Enforce
Referential Integerity & Cascade Update Related Fields.

Use a form/subform for store/manager display with StoreID field as
Master/Child links.

Use a query and set criteria of Is Null for the EndDate field to only
display the current manager.
 
Karl's method is good. It's also optimal if one assumes that more or less a
manager does only one stint of duty. But you have to realize that what is
being recorded in that second table is instances of management of a store
(not people, managers or changes) or else you will get all mixed up. You
will also be re-entring all of the information for that person for each
instance of them managing a store. A database purist would probably
create a third table to avoid this, but I would do it Karl's way. The
reentering is no biggee and maybe a plus.
 
I agree you could use a third table of Employees that would contain EmpID,
Name, Home, Pager, Cell, etc.
Then the second table would have StoreID, EmpID, StartDate, EndDate, etc.
The subform would have a listbox to choose the manager from employee list.
 
we review the manager information daily - so we usually overrite what is
existing in a record.
is there any way to keep a single table of for ex. 10 records (1 for each
store) for what is existing at that time - and create a second table with the
updating data?

thanks!
 
You can but it is not the best way.
Use a form for data entry and have an event to append the record to your
history table on update of the record.
 
Use the form as criteria --
[Forms]![YourFormName]![YourFieldName]
Use the Store identicication field from the form as criteria in the query.
 
Back
Top