Tracking History Challenge

[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!

 

[KARL DEWEY]

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.

 

[FredFred]

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.

 

[KARL DEWEY]

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.

 

[Sarah]

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!

 

[KARL DEWEY]

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.

 

[Sarah]

how can I append a record, so it only appends the changed records?

 

[KARL DEWEY]

Use the form as criteria --
[Forms]![YourFormName]![YourFieldName]
Use the Store identicication field from the form as criteria in the query.