Find - Replace - and replace previous data in another table

[clintjohns]

My question is this. I work for a local gov. and we are doing a ne
database for our Sanitation dept. We have a list of can seria
numbers that were placed at a residence which we have also. Th
situation is this. We need a way of keeping up with what can i
where whether it be in use or in storage or destroyed. We need th
database to be able to take an existing serial number(001) an
replace it with serial number(002) and then take serial number (001
and either show that it is in storage or that it was destroyed. An
ideas on how to do this. I guess it will kind of be a find an
replace and then move what we replaced to storage.
HEL

 

[John Nurick]

Probably you need three tables:

-one for Locations, with one record for each residence and additional
records for any other relevant locations such as stores, cleaning, or
workshop (if the cans ever get repaired), plus one for for cans that are
written off (destroyed, lost, stolen...).

-one for Cans, basically your list of serial numbers with additional
fields to store any other information about the individual item, such as
the date acquired.

-the third, for CansLocations, with fields for CanSerialNum and
LocationID, to actually keep track of which can is in which place, plus
a timestamp.

With that setup, the location of a Can is always shown by the most
recent record for that serial number in the CansLocations table. If a
can is moved from one place to another.

So if you withdraw can 001 to store (location -1) and replace it at
location 999 by can 002, these actions are recorded by adding two
records to the table:

001,-1,now()
002,999,now()

A byproduct of this is that it's possible to track the history of every
can should you ever wish to do so.


On Tue, 22 Mar 2005 00:21:16 -0600,

 

[clintjohns]

Thanks for the help. I will begin working on it soon