Associating records in same table.

  • Thread starter Thread starter tryit
  • Start date Start date
T

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
 
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,

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.
Click to expand...

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,

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.
Click to expand...

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.
 
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.
Click to expand...

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,

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.
Click to expand...

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
 
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.
 
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.
 
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.
 
Back
Top