Query for records

[Guest]

I need to query for all records in a table where at least a particular
participant is the same.
The fields I need are all of the following:
PartType.Participants
Name.Participants
LoanNumber.GeneralInfo

Where one Name.Participant = John

I'm trying to find commonality for all participants who are associated with
John.

 

[John W. Vinson]

I need to query for all records in a table where at least a particular
participant is the same.
The fields I need are all of the following:
PartType.Participants
Name.Participants
LoanNumber.GeneralInfo

Where one Name.Participant = John

I'm trying to find commonality for all participants who are associated with
John.

What is in the "participants" field? How are the tables related?

John W. Vinson [MVP]

 

[Guest]

The participants field contains the name of people involved in the loan (a
String). The GeneralInfo Table has the Loan Number as the primary key. There
is also a field for the participants. The Participants.tbl has names,
addresses, type of participant fields. This is my link with the
GeneralInfo.tbl.

Ericc

 

[John W. Vinson]

The participants field contains the name of people involved in the loan (a
String). The GeneralInfo Table has the Loan Number as the primary key. There
is also a field for the participants. The Participants.tbl has names,
addresses, type of participant fields. This is my link with the
GeneralInfo.tbl.

If you're storing multiple names in the Participants field you're violating a
fundamental principle of database design: fields should be atomic.

You cannot link from a free-format text field containing names to a
Participants table, at least not reliably and not easily. For one thing, names
are NOT unique; for another, they're not reliable. If your Participants field
contains

"Mike Jones, Mary Wilson Jones, Estate of Bruce Wilson"

is that a match for Michael A. Jones in the Participants table? Or maybe it's
a match for Michael E. Jones? or maybe neither of them?

The proper way to handle this is to NOT have a text field mushing all the
names together in one field, but to have a LoanParticipants table with fields
for the LoanNumber and the unique PersonID from the participants table.

Am I misinterpreting your request here?


John W. Vinson [MVP]

 

[Guest]

Each name has a unique Participant ID [PartID] and each name is a single
string.

The LoanNumber is on a separate table with a linking field for the
Participants.

 

[John W. Vinson]

Each name has a unique Participant ID [PartID] and each name is a single
string.

The LoanNumber is on a separate table with a linking field for the
Participants.

But you said:

The participants field contains the name of people involved in the loan (a
String).

I don't understand the structure of your tables, I guess! Could you please
post the tables in the format

Tablename
Fieldname <Datatype> <Primary Key>
Fieldname
Fieldname

and the relationships between your tables?

John W. Vinson [MVP]