G
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
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