Smart Record Cross-Referencing

[Tom]

Hi,


I need a "smart idea" that allows me to cross-reference a "Primary Key"
(Autonumber) in a form.

To describe the issue at hand, allow me to provide some sample data (this is
made up... but it'll highlight what I'm trying to achieve).

Let's say I have the following table/data:

RecordID LastName FirstName
1 Smith Rose
2 Jones Mike
3 Hughes Anton
4 Smith Sam
5 Hughes Bertha
6 Hughes Charlie
7 Jones Norma
8 Smith Tony
9 Hughes Denise

In this sample, there are "3 families"... Smith, Jones, and Hughes. Each
family member (e.g. "Rose Smith", "Sam Smith", "Tony Smith") has an
individual record.

Now, here's what I'm trying to achieve... Create some method that will allow
me to "connect" the 3 Smith family members (and, naturally, the other as
well).

In other words, I simply want to be able to do the following:
a. indicate that e.g. Rose is related to e.g. Sam and Tony, then
b. by default, Sam must be related to Rose and Tony, and
c. Tony must be related to Rose and Sam


Does anyone know of a smart approach how to do this? I'd appreciate if you
could provide some details as to how this could be achieved?

Thanks,
Tom

 

[Allen Browne]

Hmm. I'm not sure that having the same surname is a good basis for
concluding the people are related. You might end up with a very large Smith
family.

In general, I think you need to define these relationships yourself rather
than trust the computer to imply them. It might be that John Smith and Mary
Smith live in the same household, but are not related, e.g. if their parents
are remarried, and John took on his step-father's name. By the same token,
John Boswell and Mary Smith could have the same kind of relationship
(step-sibbings) even if they don't have the same surname.

For a suggestion on how you can define (not imply) very flexible sets of
relationships between people, see:
People in households and companies - Modelling human relationships
at:
http://allenbrowne.com/AppHuman.html

 

[Tom]

Allen,

thanks for the feedback... I simply used the "Names" scenario as an example.

Let me approach it a different way. I'll try via some "News Headlines".
Let's say, we have records with the following headlines in the "subject
field".

Record #1: "Superbowl -- Steeler vs. Seahawks."
Record #2: "Katrina hit Lousiana hard."
Record #3: "Acme lays off X number of employees."
Record #4: "Devastation after the hurricane."
Record #5: "Pittsburgh wins for the 5th time."
Record #6: "Average salary for Pro-NFL players is $$$."
Record #7: "Economy has grown n%."
Record #8: "Movie Star Wars is still a hit."
Record #9: "FEMA should be better prepared."


Okay, most of my headlines are somehow related (e.g. "Football",
"Hurricane", "Economy"). More specifically,
Football is (could be) linked to: 1, 5, 6
Hurrance is (could be) linked to: 2, 4, 9
Economy is (could be) linked to: 3, 7 ... and maybe #6
Entertainment seems to stand by itself (record #8).

As far as I'm concerned, it doesn't really matter which record is linked to
what other record(s).

I stimply want to establish creating dynamic linkages so that analysts can
draw conclusions from the associations. Now, what conclusions isn't
important either.

Just to be sure, let me provide you some more detail. Again, in my
application the "headline" = "subject". Employees will create individual
records and ask for funding. Sometimes, one individual (funding) request
is assocated/dependent on another request (it might be even outside the
domain of the applicant).

What we're hoping to identify is the following:

- if a request for funding is denied, we'd like to find out this the
potenitally denied request will a domino effect on other requests.
- so, if I were not to fund "Subject A"... I'd like to be aware that
"Subject N" may not get accomplished as well as it does require the
completion of "Subject A".
- I do not need to be specific and identify which "Subject" is the "parent"
or the "child"... I simply want a method that will enable me to quickly
identify those associations (via query). Other individuals will handle to
figure out what those impacts are.

Still possible? If yes, how?

Tom

 

[Allen Browne]

With your news headlines example, I imagine this would involve creating a
series of topics (football, economy), with associated keywords (many
keywords to one topic), and then matching the stories to to categories (many
to many) based on what keywords are found in the headlines.

Trying to automatically identify possible connections between funding
requests based on names or keywords sounds fraught with danger. I'm not sure
I want to even start into that field, as the potential for false positives
and missed connections would need several days of examination to even begin
to get something like a basic "possible alert" scenario that would be even
remotely useful, and if the computer is actually making some kind of
decision or recommendation based on these supposed connections, it could be
a good case for a lawsuit against you later.

 

[Tom]

Allen,

thanks... I appreciate the advice... again, I just used these as "examples".

All I need to come up with some automation method that will do the
following:

a. I link (check checkbox, add subrecord or whatever method) record #1 to:
record #4 and #8,
b. I then want to make automatically create a reference in #4 to: #1, #8
c. as well automatically create a reference in #8 to: #1, #4

That's all... is there a way to automatically create these references in b &
c once I created the reference (manually) in a?

Tom

 

[Tom]

Oh, forgot to mention... the linking can simply be done via Primary key
(autonumber).

Tom

 

[Pat Hartman\(MVP\)]

Rather than trying to save us from the complexities of your application, why
not just tell us what you have? So far you've made up three examples that
could be satisfied by three different solutions. The first would have
employed a FamilyID in each record to group family members. The second
would have employed creating a topic table and then a table to relate each
story with one or more topics. And I don't have enough information to
speculate on what the third solution should be.

 

[Tom]

Pat,

sorry for the confusion... I tried to provide sufficient info, but you're
right... it was too much info.

All I need to come up with some automation method that will do the
following:

a. I link (check checkbox, add subrecord or whatever method) autonumber #1
to: autonumber #4 and #8,
b. I then want to make automatically create a reference in #4 to: #1, #8
c. as well automatically create a reference in #8 to: #1, #4

That's all... is there a way to automatically create these references in b &
c once I created the reference (manually) in a?

Tom














From: "Pat Hartman(MVP)" <[email protected]>
Subject: Re: Smart Record Cross-Referencing
Date: Monday, February 06, 2006 10:38 PM

Rather than trying to save us from the complexities of your application, why
not just tell us what you have? So far you've made up three examples that
could be satisfied by three different solutions. The first would have
employed a FamilyID in each record to group family members. The second
would have employed creating a topic table and then a table to relate each
story with one or more topics. And I don't have enough information to
speculate on what the third solution should be.

Oh, forgot to mention... the linking can simply be done via Primary key
(autonumber).

Tom

 

[Pat Hartman\(MVP\)]

There is no automatic way to do this. Your relationship goes beyond the
normal many-to-many. In a many-to-many relationship, you would insert a
record for #1 related to #4 and another for #1 related to #8. The converse
could also be derived from these two rows - #4 related to #1 and #8 related
to #1. It will be up to you to make any other relationship yourself. That
means that you would also need to add #4 to #8. You never need to add the
converse relationship since it is derived. So, adding #4 to #8 would
automatically give you #8 to #4.

You have some coding to do since creating the relationships between related
items will be tricky. I presume that more than three items can be related
so you'll need a loop to get all the "odd" relationships added.

 

[Tom Ellison]

Dear Tom:

What you keep referring to would be a self-referencing table. I'm not sure
this is the best approach for what you say you want. I rather like Allen's
recommendation to create categories and to put them into externally (in
another table) defined groups. For a single tier hierarchy, with no one
really being the "parent" of all those belonging to the same category, then
he has it right.

Your questions seems to center increasingly on doing this "automatically".
Well, the algorithm to do this is going to be entirely custom.

If you handed this data to some stranger to perform this categorization, how
would you explain to them how it must be done? You must make these
instructions very step-by-step and exact. Program that, and you have it.

So, this really depends on the data you have, how exacting it is, and how
exactly you can define and program how to do it.

Tom Ellison