Relate words in memo/text field to another table

[Annette]

I recently reviewed an application that did something cool. For
purposes of explaining, let's say we have three tables. One table
defines case information (caseid, date of the case, case number and
case description). One table contains people involved in the case
(caseid, name, shortname, other supporting information). The last
table contains facts (factid, caseid, date of fact, text of fact).
When typing in the text of fact, in what could be a memo or text
field, and you type the shortname, a link is made back to the people
table of that case.

So let's say you have three people in the people table with short
names of AndersonA, MillerP, JonesS and when you are in the fact table
you enter a fact "AndersonA and JonesS went shopping", and "AndersonA
and MillerP went shopping", links would be made back to the people
referenced in that case. So if later I go to the people table I will
see that AndersonA has 2 facts assigned to him, MillerP has one fact
assigned to him and JonesS has one fact assigned to her. I have seen
this working in an application and as soon as you enter a fact, typing
in "AndersonA" will display as a hyperlink to the people table and as
you are typing the letters it appears that it is searching the people
table as you type because it will auto-complete the text similar to
being in a combobox. The beauty of this is that the field is a text/
memo box but has the relation back to the people table without having
to enter multiple records and assigning.

I understand that I could enter a fact and then assign people to it,
but a fact could have alot of people attached to it and it could be a
lot of data entry.
"AndersonA and JonesS went shopping" with a combo box assigning to
AndersonA AND
"AndersonA and JonesS went shopping" with a combo box assigning to
JonesS

Any ideas?

 

[Access Developer]

Just for the record, Access works very nicely with structured data (and can
be extended just a bit to do _some_ unstructured searching). It seems to me
that what you are describing is "full-text search" capability, and Access is
not the best (or even, a good) tool to create that type of application.

Your description is unclear... you say that the relationship is between the
Case table and each of the People and Facts tables, but your example
indicates that the relationship is between People and Facts.

If I were a bettin' man, I'd wager some pocket change that the application
you describe, if it is as broad in scope as implied, was not created with
Microsoft Access. I'd also wager that there's just a little more to your
requirements that to search the text of a fact entry to find people...
Suppose, for example, that instead of "AndersonA" and "JonesS", the fact
read "Annette and Sarah" -- you'd want those to search out Annette Xavier
Anderson and Sarah Frances Jones, wouldn't you?

You might well be able to do what you want with Access, but it'd take a good
deal more knowledge of the problem space than we now know, heavy expertise
with Access and relational database design, and lots of time and effort to
design and implement the application. And, almost certainly, if the
application you observed is commercial software, it would be far, far more
cost-effective to obtain a copy of it than invest the study, time, and
energy to be able to create Access application to perform a similar
function.