Lookup column relating items in same field

[rmroberts76]

I think this is a simple problem, but I can't figure it out.
I have a list of scholarly articles (one field of article titles).
Each article has numerous references. I would like a lookup column that relates one article title to the numerous other article titles referenced.
Any suggestions?



Thanks,

rmroberts76

[NTC]

really your issue is classical table relationship design. if your tables are set up with the proper cross referencing values then this is no problem - it is what a relational database is all about.

I can't tell if your question is how to implement this for the viewer on the screen in terms of subforms vs comboboxes....or how to implement initially at the table level....

[rmroberts76]

really your issue is classical table relationship design. if your tables are set up with the proper cross referencing values then this is no problem - it is what a relational database is all about.

I can't tell if your question is how to implement this for the viewer on the screen in terms of subforms vs comboboxes....or how to implement initially at the table level....

I understand your question.
I've made several databases in the past with numerous tables related to one another; however, I've never made relationships within the same field.

So, my question is absolutely "how to implement initially at the table level."
I really don't want to make a separate table of the same article titles. Why have two tables with exactly the same field with exactly the same data in them? And then updating them both would be a hassle.
Thanks for taking a look at this.

[NTC]

If I understand your question (and I'm not sure still that I do) - - will answer with this example: Consider the telephone book; it is 1 table.

You select the name Tom Smith but want then to look at also all other names somehow logically linked. What link logic? Let's say everyone with the same zip code. That zip code is in a different field of the same table.

You can set up either a combo/list box - or a subform - - to look at the same table and that underlying query that is the source for the combo/list/sub can have the criteria(filter) of whatever zipcode value has been selected in the main form....so it will have everyone with the same zipcode as the person you select.

[kimmer]

Please pardon my interruption, but I think I understand the question because I had someone else recently with similar (it is still fresh on my mind).

The poster wants to relate ArticleID to ArticleID; an article can refer to other articles

In addition to the Articles table, create a table ArticleReferences... the fields are AutonumberPK, ArticleID, ReferencedArticleID.

Add the Articles table to the Relationship window twice.
Relate ArticleID in the Articles table to ArticleID in ArticleReferences. Relate ArticleID in the table "Articles_1" to the ReferencedArticleID.

When you make a query, you will need to add the Articles table to the query twice.

[rmroberts76]

Kimmer - simple and easy. Thanks a lot.
Did you create a subform to list references?
I'd be interested in how that was displayed.

Thank you.

[kimmer]

have a look. Open frmArticles.

[rmroberts76]

Thanks for showing me your form. This is what I've got.

[kimmer]

Hey, it looks great! But your articles aren't nearly as funny as mine. And you're welcome... I threw the tables and form together to test before I told you it would work. I'm sure glad it worked out for you.