Associating records in same table.

[tryit]

Suppose I have a table, as follows:

ID Record
1 item1
2 item2
3 item3
4 item4
5 item5
6 item6
7 item7
..
..
..

And I want to create a "see also" field so that record ID 7 contains a
see also list referring to records 1, 3, and 5.

How would I set this up? I know how I would do this if the list were
of items in another table. I would set up a junction table and do it
with a subform. However I'm not sure how I would associate items in
the same table.

TI

 

[Gina Whipp]

tryit,

The way you said you would have to do it as the way you have to do. You
cannot create a circular reference within a table.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[KARL DEWEY]

You need the ID_Record to be primary key. Use a matching field for your
'See_Also' field (PK - Autonumber, FK - number long integer).

Open the Relationship window, put the table twice in the window (Access adds
a sufix of '_1' to the second instance). Click on the primay key field of
the first instance and drag to the 'See_Also' field of the second instance.
Select Referential Integerity and Cascade Update.
Use a form/subform to display Item/See_Also's. When you select a record
using a combo box to the subform it will automatically insert the Item's ID
to the See_Also record.
An item can have multiple See_Also but a record can only be 'seen' by one
item. If you need more than one then that is where you would need a junction
table.

 

[tryit]

tryit,

The way you said you would have to do it as the way you have to do.  You
cannot create a circular reference within a table.

But the records are in the same table. The way I said was if the
records referred to were in another table. Can I create a junction
table to a query of those records? I'm confused.

 

[Gina Whipp]

TryIt,

I meant the way you said ...with the junction table... Though Karl has
seggested a way that did not occur to me.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

tryit,

The way you said you would have to do it as the way you have to do. You
cannot create a circular reference within a table.

But the records are in the same table. The way I said was if the
records referred to were in another table. Can I create a junction
table to a query of those records? I'm confused.

 

[tryit]

You need the ID_Record to be primary key.  Use a matching field for your
'See_Also' field (PK - Autonumber, FK - number long integer).

Open the Relationship window, put the table twice in the window (Access adds
a sufix of '_1' to the second instance).  Click on the primay key fieldof
the first instance and drag to the 'See_Also' field of the second instance.  
Select Referential Integerity and Cascade Update.
Use a form/subform to display Item/See_Also's.  When you select a record
using a combo box to the subform it will automatically insert the Item's ID
to the See_Also record.  
An item can have multiple See_Also but a record can only be 'seen' by one
item.  If you need more than one then that is where you would need a junction
table.

Hi Karl.

It sounds like both you and Gina are suggesting that, if I want to
have an association between one record and multiple records in the
same table, that I would need to set up a junction table between two
instances of the same table in the Relationships window. Is that the
way it would work?

Unfortunately, I don't think I can modify the primary key at this
point. I have tried that before and I get a warning saying access
will not allow that since the db is too big. Is that really
necessary?

Thanks,
TI

 

[Gina Whipp]

TryIt,

Yes that is what we both are suggesting BUT now the concern is the size of
your database. Have you run compact and repair? How big is your database?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

You need the ID_Record to be primary key. Use a matching field for your
'See_Also' field (PK - Autonumber, FK - number long integer).

Open the Relationship window, put the table twice in the window (Access
adds
a sufix of '_1' to the second instance). Click on the primay key field of
the first instance and drag to the 'See_Also' field of the second
instance.
Select Referential Integerity and Cascade Update.
Use a form/subform to display Item/See_Also's. When you select a record
using a combo box to the subform it will automatically insert the Item's
ID
to the See_Also record.
An item can have multiple See_Also but a record can only be 'seen' by one
item. If you need more than one then that is where you would need a
junction
table.

Hi Karl.

It sounds like both you and Gina are suggesting that, if I want to
have an association between one record and multiple records in the
same table, that I would need to set up a junction table between two
instances of the same table in the Relationships window. Is that the
way it would work?

Unfortunately, I don't think I can modify the primary key at this
point. I have tried that before and I get a warning saying access
will not allow that since the db is too big. Is that really
necessary?

Thanks,
TI

 

[tryit]

Hi, Gina. Yes, multiple times. IMO (not Access's) a 20MB db is not
large. Perhaps I'm wrong.

Is adding to the PK necessary? I'm not sure I see why.

 

[Gina Whipp]

No 20MB is not that large, I just have never eceived that message trying to
modify a PK. As for PK... you NEED the PK to FK relationship.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Hi, Gina. Yes, multiple times. IMO (not Access's) a 20MB db is not
large. Perhaps I'm wrong.

Is adding to the PK necessary? I'm not sure I see why.

 

[KARL DEWEY]

I agree there is no need to change the primary key in any of the two methods
- Self-related or junction table.
Note that I said the self-related can only have one seen-by as in a child
can only have one mother even though a mother can have many children.