Self-linking table in ADP

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Charax - 15 Aug 2005 02:07 GMT

In an Access MDB, I can add a second instance of a table to the
Relationships Diagram as if it were another table. Then I can use a linking
table to provide a many-to-many relationship from the table to itself. By
enforcing referential integrity, the duplicates can be tracked. Example:

tblPhotos tblDuplicates tblPhotos_1
PhotoID ----------------- PhotoID /----------- PhotoID
Filename PhotoID_1 -------/ Filename

pk=PhotoID /PhotoID pk=PhotoID
inherited
pk=<
\PhotoID_1

How can this be done in ADP?

Thanks,

Chris Hopkins

Reply to this Message

Sylvain Lafontaine - 15 Aug 2005 05:44 GMT

In exactly the same way as with MDB.

However, I seriously doubt the necessity of using a self-referencing
relationship to track duplicates. It would be easier to put the photos in
their own table. Self-referencing relationship should be used only for
hierarchical datas.

Signature

Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

> In an Access MDB, I can add a second instance of a table to the
> Relationships Diagram as if it were another table. Then I can use a
[quoted text clipped - 17 lines]
>
> Chris Hopkins

Reply to this Message

Charax - 15 Aug 2005 10:58 GMT

Silvain,

Could you be more specific on how to do this because I do not understand how
it is "exactly the same as with an MDB"? I think there is no Relationships
Diagram in ADP, but instead the Database Diagram objects -- where if I try
to add a second instance of the table, I get this warning:
"One or more selected tables are already on the database diagram.
"You can only have one copy of any table on a database diagram...."

The photos are already in their own table, and I see this as a many-to-many
relationship on the same table, not self-referencing. My question is how to
track the duplicates with a linking table that has PhotoID and PhotoID_1
fields from the tblPhotos with referential integrity enforced -- or some
other method that works in ADP.

Thanks for your help, but please give a little more detail in your answer
because I am new to ADP after many years of using MDBs.

Regards,

Chris Hopkins

> In exactly the same way as with MDB.
>
[quoted text clipped - 22 lines]
>>
>> Chris Hopkins

Reply to this Message

Sylvain Lafontaine - 15 Aug 2005 15:02 GMT

I'm sorry but you're right: I misread you question and took more attention
to the self-referencing question than the second instance of the table
question. (You can indeed add a copy of the same table with ADP but only
when designing queries (views and procedures), not directly in the database
diagrams).

In ADP's database diagram, you must make the relationship directly from the
table to itself by dragging the foreign key to the same table (which you
cannot do in an Access MDB file). When creating Views or Stored Procedures
in design mode, you must process the same way as with Access MDB file
because you must give an alias name to the second instance in the Select
statement.

Signature

Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

> Silvain,
>
[quoted text clipped - 44 lines]
>>>
>>> Chris Hopkins

Reply to this Message

Charax - 15 Aug 2005 15:22 GMT

Many thanks for your kind help, Sylvain. Your advice should get me on the
right track.

Best regards,

Chris Hopkins

> In ADP's database diagram, you must make the relationship directly from
> the table to itself by dragging the foreign key to the same table (which
> you cannot do in an Access MDB file). When creating Views or Stored
> Procedures in design mode, you must process the same way as with Access
> MDB file because you must give an alias name to the second instance in the
> Select statement.

Reply to this Message