I have a database I have created that lists all of our standards. It has past and current editions. When I create a new record, I would like certain fields to reference the material (part) numbers from the previous edition so I know which ones to take down from our online store. Is there a way to make fields reference other fields in other records based on certain criteria?

Hello Mykol,

Fields in other records within a db can be referenced by using one or more self join relationships.

The criteria for selecting which records to access will be determined via the fields you choose as the match that the relationship(s) are based on. Since either or both of the match fields can be calculating fields, the criteria for the match can be as complex or as simple as you wish.

The two fields that relate to each other are designation and edition. The designation I could see making a self join relationship, but since I want the previous edition, shouldn't there be a calculation that has the last edition number and then relate edition number to previous edition number? I'm not sure if I'm getting this right. I've never gotten the total concept of self-join relationships.

The two fields that relate to each other are designation and edition. The designation I could see making a self join relationship, but since I want the previous edition, shouldn't there be a calculation that has the last edition number and then relate Edition Number to Previous Edition Number? This way I could make the Previous Material number show up in a lookup field.

I'm not sure if I'm getting this right. I've never gotten the total concept of self-join relationships.

Sounds from what you say as though you are on the right track.

If you create a calc field which is Designation & Edition, and a second calc field which is Designation & (Edition - 1) and then set up a self-join relationship which matches the latter with the former, you will be able to access data from the record which holds the previous edition on it, from any record in your db.

Give it a try - once you get going you'll probably find that it will all start to fall into place.

Brilliant! I was just missing that last piece. It never dawned on me to make the calc fields to relate to each other. After that, it was all magic.

Thanks a ton.

Mykol