Data Preserve?

[Mike]

Hello all! Thanks for all that you do ahead of time. Without getting too
far into detail I would need some direction as to how to keep some data.
Please let me know if I need to get into details.

I currently have a database with salesman information and from time to time
each salesman is moved to another location. But when the salesman is moved
so is his location information. Usually the user adminstering the database
would just locate the salesman by name and edit his location information.
But if this person is moved, I would like to be able to keep records of his
past sales in his past locations. Is there a way to acheive this without
harming the current structure of the DB?

Mike

 

[Tom van Stiphout]

On Mon, 19 May 2008 06:18:05 -0700, Mike

Your current db design likely does not support this. Rather you need
something like below to track salesmen at locations over time.
tblSalesmen
SalesmanID PK
Firstname
Lastname
'Not a location field
etc.

tblLocations
LocationID PK
LocationName

tblSalesmenAtLocations
SalesmanID PK
LocationID PK
DateStarted PK
DateEnded
(DateStarted also in the PK because salesman could come back to a
previous location)


-Tom.

 

[Klatuu]

If you only change the location in the table that identifies the sales person
and do not change the location in the sales table, then all that person's
sales in the previous location would remain in that location.

Now the issue is being able to show that person's sales in all the
locations. For that, you need an addition table. It should have two fields,
one to identify the sales person and one to identify the location. The
relationships should be sales person one to many to location table and
location one to many to sales table. You would need this table because many
to many relationships are not possible.

Now to show the data on reports or forms, you would need to use a query that
joins the sales person table and the location table.

 

[Jerry Whittle]

What you are describing is a many-to-many relationship between the salesmen
and locations. Therefore you'll have to add another table minimum. It would
be a linking, bridging, or connecting table between the Salesman and
Location. This will break the M-M into two 1-M relationships. Of course since
you have existing data, it will take some work to migrate the existing data.

It might be possible to hang a Location table off the Salesman table with
the StartDate and EndDate at that location. If the Sales table has a date
field, you could compare it to the dates in the Location table to see where
they were when the sale was made. This is not optimal and could requires some
complex queries and code.