Modifying records in a table

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

Hello all,

Here is something that's been bothering me, and hopefully
someone will be able to help:

I have a table (call it tblTC) with many records. One of
the fields is called TRs, and normally in that field you
can find a 10 character identifier of a TR, which
corresponds to a record ID in another table (call it
tblTR).

The problem is that some of the fields have been entered
in a wrong way: some contain more than one identifier,
separated by a comma. This means that one record in the
tblTC table can refer to many records in the tblTR table.
I want to have one tblTC record for each associated TRs,
so that I can link the two tables and not miss out info.

Does anyone have an idea of how to do this?
Many thanks in advance!
Steve
 
Steve said:
Here is something that's been bothering me, and hopefully
someone will be able to help:

I have a table (call it tblTC) with many records. One of
the fields is called TRs, and normally in that field you
can find a 10 character identifier of a TR, which
corresponds to a record ID in another table (call it
tblTR).

The problem is that some of the fields have been entered
in a wrong way: some contain more than one identifier,
separated by a comma. This means that one record in the
tblTC table can refer to many records in the tblTR table.
I want to have one tblTC record for each associated TRs,
so that I can link the two tables and not miss out info.
Click to expand...


You need to explain the relationship between the two tables
in more detail before anyone can provide a definitive
response.

In general, it sounds like you want to be able to deal with
situation where one TC can have many TR records. In this
kind of situation, you should NOT have a TC record for each
TR and a TC record should NOT refer to one or more TR
records. Instead you should have a field in the TR table
that contains the identifier of the **single** related
record in the TC table. This allows you to use a query that
Joins each TC record to all of its related TR records.
 
Exactly what I would like to do, but the problem is the
database is like this at the moment (I am doing an import
from a text file) and I need to reformat the database to
be able to make relationships.

Any idea of how to do this?

Thanks,
Steve
 
Exactly what I would like to do, but the problem is the
database is like this at the moment (I am doing an import
from a text file) and I need to reformat the database to
be able to make relationships.

Any idea of how to do this?
Click to expand...


Well, you could use code to parse (Split function) out the
individual TR ids and Execute an Insert Into query to add
more TC records, but you won't be able to make much a
relationship of the resulting conglomeration unless you run
another procedure to delete the "duplicate" TC records and
add a TCid field to the TR records.

OTOH, maybe you don't have a many TR to one TC relationship.
If you can have many TRs for each TC and Each TR might be
used by many TCs, then you really have a many to many
relationship and need a third table (Junction) to keep track
of what's related to what.
--
Marsh
MVP [MS Access]


 
Back
Top