Cross Referencing Records from a Single Table

[pmcleod]

I tried the idea sugested in post (http://www.access-programmers.co.uk/forums/showthread.php?p=423080#post423080) but it didn't seem to give the cross referencing that I had hoped for .... example 1 is related to 4,3, and 5 while 6 is related to 1 thus implying an extended relationship to 4,3, and 5.

In a standard one-to-many I'd look at record 1 and see that it is related to 4,3,5 but if I were to look at record 6 I'd only see that it is related to 1. How would I set up the table relationships to drill further to see that 1 is also related to 4,3,5?

How do you do a many-to-many from one table back to that same table?

tblEvent
EventID(AutoNumber) EventTitle(Memo)
1 memo content
2 data
3 more memo content
4 some info
5 more stuff
6 other text

tblEventRelationships
EventParrent EventChild
1 4
1 3
1 5
6 1
3 2

In addition ... how would one set up the integrity to prevent loops from forming?

 

[llkhoutx]

I've attached an zipped A2K mdb which illustrates what you want.

 

[pmcleod]

llkhoutx,

Thank you for the sample but, to me, it looks like a simple one-to-many relationship.

To relate an event to its self within the same table I thought that I would have to create a many-to-many using the event table and a join table.

I knew what I had to do, but could not seem to get the results that I was looking for.

We wound up getting into this on another string (http://www.access-programmers.co.uk/forums/showthread.php?t=94322&page=2&pp=15), and in In Pat's last post, she was kind enough to give me instructions .... and yet it did not seem to work for me.

"EventID has an RI enforced one-to-many with the first ID in the related table, which I've renamed to EventIDChild.
EventID has an RI enforced one-to-many with the second ID in the related table, which I've renamed to EventIDParent."

"However I can create a record where ParentID = 1 and the ChildID = 2. Which means that I don't understand how to force both entries, as you suggested. Nor did my attempt to understand "...lower ID in the first event ...", which I took to mean Field One is the child (lower), while field 2 is the higher, or parent."

I've attached my attempt to understand what Pat was kind enough to write.

 

[Ian Mac]

Hi,

Can you explain what you are trying to achieve with your question.

That sounded rude, by that I mean.
What I need is for me to run a report, which does x y z.

With some specfic outputs you need,

by what you have described it looks like adding only a few more line of data in the main table is going to cause looping problems as you predict.

I think there is going to be better way to store your information.

 

[The_Doc_Man]

If this were a Pascal or Ada or C++ structure, no one would hesitate to answer the question correctly. Access doesn't change structural concepts.

When you have the relationship described here, where you have parent, child, grandchild relations, you would use (for example) two tables.

tblPersons
PersID, autonumber, prime key
... other personal data

tblRelationships
PersID, long int, foreign key many to one for tblPersons:ersID
RelnID, long int, foreign key many to one for tblPersons:ersID
(option: code/key/name for relationship)

where you have one entry in the tblRelationships table for every relationship. You may (correctly) choose to make relationships unidirectional if that is right for your app.

OK, so let's look at the case described. We said that 3,4, & 5 are related to 1 and 1 is related to 6.

Assume tblPersons has records 1-6 inclusive. tblRelation will contain these pairings:

1,3
1,4
1,5
6,1

OK, it is clear that you can iterate across the tblPersons to find all records in tblRelationships that have a PersID to match the one in the PersID table. That takes care of the single-generation relationships. You would find counts of 3 for PersID 1 and a count of 1 for PersID6.

So now, the question is, how do you do multi-generational relations? Simple, if you understand recursive programming. Sort tblRelationships to group by the PersID first, then by RelnID second. For each record, test whether there exists a record with a PersID that matches the RelnID of the record you are examining. For instance, with DCount.

If there exists at least one record where the count of tblRelationships records with that PersID is > 0, then you have to go back and check whether the record selected by THAT PersID has a RelnID that points to another record, too.

Let's look at PersID=1. The count is 3 and the records that would come back for the query include (1,3), (1,4), and (1,5). No biggie so far. Look for records with PersID 3, 4, or 5. You have no tblRelationships records that match, so from PersID, you have only one generation downward.

When you test the (6,1) record, however, the search for tblRelationships records with PersID of 1 (to match the RelnID of the 6,1 record) shows a count of other than zero. That's due to the (1,3), (1,4), and (1,5) records. And that is how you know a multigenerational record has been found.

In Pascal, Ada, or C++, all you would do is make the link (pointer) zero to show "no relationship." But in Access, because it is based on the "sparse" style of data storage, you don't store a zero. Do not store ANYTHING, because the absence of a record in Access is a valid finding. If you have that linking record, you have a relationship. If you have no linking record, you have no relationship. See, e.g., tblPersons for PersID 2.

Or did I just confuse you? (It IS late in my day, after all...)

 

[pmcleod]

Sorry ... was away, family business.

I have, as far as I understand, set the relationships up as you, Ken, llkhoutx, pat, and Ian have so patiently described. I completely understand the concepts, in fact manage projects far more complicated than this, however I'm not a developer ... and so I don't know how to apply those concepts.

I can take another's code and modify it to suit my needs, but to write something myself .... no can do.

Developing a routine to deal with multi-generational relations through recursive programming is beyond my abilities.

I'll have to pull back my requirements considerably, back to something that I can do myself. Either that ... or find a developer with some idle time .... LOL