SERIOUS query problem with duplication

  • Thread starter Thread starter svetelina
  • Start date Start date
S

svetelina

Hello all,
I have a problem with duplicated prim. keys.
I have a table which contains the same records twice, but
the specification is that since the records belong to two
diff. subgroubs of records within the table, I have to
keep them both. Example:
table 1
1 2 4 6 9
1 3 4 6 8
1 2 4 6 9
1 7 4 6 8
1 8 4 5 9

Table 2
1 2 4 6 9
1 7 4 6 8

The order of the tables s has to be preserved, and they
need to be linked. In table 1, first two records form one
group, sencond 2 another. Talbe two has to be matched to
group 2 but there is a duplicated record.

How can I link the tables? How can I make the prim keys
unique?

Sveta
 
I think I understand your question so let me take a stab... Not sure if you
meant that "1 2 4 6 9" is stored in a single field or multiple fields, I
will assume a single field

What you might want to do is set multiple fields in Table1 as the primary
key and then create multiple relationships between the related tables. In
design, first remove the existing PK, then select your previous PK field and
also the field that stores the subgroup name and then right-click and choose
PK. This will set a multi-field PK. Any one field in the PK can be
duplicated as long as the other field(s) are unique. This will allow a
record 1 2 4 6 9, group A and 1 2 4 6 9 group B. Thus allowing unlimited 1 2
4 6 9 records as long as each one is in a different group. And also the
reverse - unlimited Group A records as long as they all have unique numbers.

Then you will need to create a multi-field relationship between the tables
in order for them to relate correctly. This would likely mean creating both
field in the related table as you used in the main table's PK, then linking
them accordingly in the relationship windows. When I was first realizing
this could be done it took a little trial and error to get them right but
afterwards I think I took a small leap forward in understanding data
relationships.

Another possibility would be to create an Autonumber field in the main
table. This would not be part of the PK but would inherently remain unique.
You would still need the multi-field PK in the main tabl. The related table
would have just a number field related to the main table's Autonumber field.


Hope this helps
Tony
 
Back
Top