Find & replace and replace previous data

[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 garbage ca
serial numbers that were placed at a residence. The situation i
this. We need a way of keeping up with what can is where whether i
be in use or in storage or destroyed. We need the database to be abl
to take an existing serial number(001) and replace it with seria
number(002) and then take serial number (001) and either show that i
is in storage or that it was destroyed. Any ideas on how to do this.
guess it will kind of be a find and replace and then move what w
replaced to storage.
HEL

 

[Guest]

The answer to your question depends upon how your tables
are structured. This is what I would recommend:

First, you should have a table that has a list of
residences with whatever fields you deem necessary
pertaining to just the residence but not the can (ex.
residentname, house#, street name,... whatever). Of couse,
you would need an ID number as the primary key.

Next you should have a table that contains the can's serial
number(which can be this table's primary key), it's status
(use, storage, destroyed). Then you need to follow one of
the two listed situations...
Situation 1: If it is only possible for a residence to have
one can at a time, you should included in this list of
fields one to hold a residence's ID number.
Situation 2: However, if it is possible for a residence to
have more than one can, you will need a third table that
will simply be a log of where the cans are. You would do
this by having a field for the residence's id number and
one for the can's serial number.

The next step for situation 1 is, of course, a little
easier to deal with. You can then create a form that will
show you the can records. This form should include the
following fields: the can's serial #; the residence id; and
an option box or combo box that displays the can's status
(depending upon how handy you are with coding option boxes,
a combo box is easier to create). You can also include a
subform on this form that will display the residence
information linked to the can's residence id. You can use
the subform wizard to set this up. (I would also recommend
that the residence id field in the main form be a combo box
that you create with the wizard. Have it lookup both the
residence id field and another identifying field or two,
maybe the street address. You can set this up so that the
field stores the residence id but shows the street address,
which is more helpful to you when you need to select an
address for the can.)

So, when you need to change the status of the can, you find
the can's record based upon the serial number, change the
status and the residence id.

Now if you actually have Situation 2, you can still build
the above form but you would need to base it on a query
that links the can table to the log table by the can's
serial number (which appears in both tables) so you can get
to the residence ID and therefore the residence information.

I hope that this is helpful! Good luck!

Andrea

 

[Larry Daugherty]

At a minimum, you'll need two tables: Site and Container

Table Site would need a Site ID, a description of the location and
fields for other notes regarding that site, such as the type and
number of container required to service the site. Storage and
Destroyed would be among the sites.

You'll need a form to enter and maintain Site data.

Table Container would need an identifier for each container (do NOT
use the provided Autonumber datatype for that), a Status field,
fields for dimensions or capacities, color, other physical description
and any notes. Since the containers will change the most over time,
your major database maintenance activities will focus on tracking the
containers.

You'll need a form to enter and maintain the Container data.
Typically you'd have a combobox on this form that would allow you to
pick the site for the current container from the drop-down list.

Eventually, you'll probably create some reports.

HTH

 

[Larry Daugherty]

Oops! I forgot to address the issue in your post title. I hope that
you've correctly inferred that my suggested solution doesn't work as a
Find and Replace.

As a container's status changes in time it is reflected against that
container. I.e. Container 099 may have been at Site 035. When it is
hauled in for storage its STATUS changes from Site 005 to Storage. A
refurbished container might go to Site 035.

HTH