Select Query based on value of field in record

[Matt]

I have a table that has our asset inventory with tag_number as the primary
key. We have some records of computers that have modems. In the modem
field, we have the modems tag_number. So, there are records of just the
modems, then there are also records of the computers with the modem
tag_number within the record. My question is, is there a way to run a select
query on the value that is in the modem_tag_number field of the record for
the computer. Say I have a button that runs a query based on the
modem_tag_number field when clicked. Is this possible and then if so, how
difficult would it be to have an update query update fields in the modem's
tag_number record to the values in the computer's tag_number record that
contains the modem_tag_number. I know this is confusing, I hope I explained
it well enough. Thanks. Matt

 

[Chaim]

Are these all in one table? In other words, you have a record that looks
like:

(comp_tag, <other fields>, modem_tab, <modem related fields>)

Is there a related modem table? It sounds like there is a normalization
problem here.

Having said that, if the modem tag_no is a separate field you can simply
select on that field by saying WHERE modem_tag_no = <whatever the modem tag
no is>. If you want to check the modem only records, I suppose you could add
a condition like: AND computer_tag_no is null.

But this really sounds like a problem in normalization. IMHO, there should
be a master Computers table with a Modems table that contains the tag no of
the computer in which the modem is installed. There is then no need to fill
all of the empty fields for the modem only records- there are no modem only
records in the Computers table. Saves a lot of space into the bargain if
you've got a lot of computers.

Makes life a whole lot simpler if things are normalized properly.