Selecting Records with Matching Values in 4 different Fields

  • Thread starter Thread starter Lee
  • Start date Start date
L

Lee

I'm trying to write a query that will match values in 4 different fields.

Table Name1 Name2 Name3 Name4

Record 1 Luke Mark John Paul
Record 2 Simon Mark John Richard
Record 3 Mark Simon Carl Luke

How can I run a query that will match values between all 4 fields;

Luke in Record1 - Name1 matches Record3 - Name4
Simon in Record2 - Name1 matches Record3 - Name2
Mark in Record1 - Name2 matches Record2 - Name2 and Record3 - Name1
John in Record1 - Name3 matches Record2 - Name3

And so on.........

Access Wizard is no good, only works if all 4 fields contains an exact
match. Do I need to write 4 separate queries or can I write one?

Any suggestions?
 
I'm trying to write a query that will match values in 4 different fields.

Table Name1 Name2 Name3 Name4

Record 1 Luke Mark John Paul
Record 2 Simon Mark John Richard
Record 3 Mark Simon Carl Luke
Click to expand...

This is an improperly designed table: you've got a one (record) to
many (names) relationship; it should better be structured as two
tables in a one to many relationship.
How can I run a query that will match values between all 4 fields;

Luke in Record1 - Name1 matches Record3 - Name4
Simon in Record2 - Name1 matches Record3 - Name2
Mark in Record1 - Name2 matches Record2 - Name2 and Record3 - Name1
John in Record1 - Name3 matches Record2 - Name3
Click to expand...

In all possible combinations!? Whew...

Ok, create a Self Join query: add your table to the query design
window TWICE. Access will alias the second instance by adding _1 to
the table name. Don't put any Join lines in the query.

Instead, as a criterion under table.Name1 put

=[table_1].[Name2] OR [table_1].[Name3] OR [table_1].[Name4]

and analogous criteria under the other three names; to prevent records
from finding themselves, or being seen twice when Luke in Record1
matches Luke in Record3 while Luke in Record3 also matches Luke in
Record1, put a criterion on Record Number of

< [table_1].[Record Number]
 
Cheers for the reply

If you haven't guessed I'm new to this!

I'm a bit lost can you give a idiots reply?
Click to expand...

Well... you'll need to learn to write queries; and you REALLY should
do some studying about normalization.

Again - This is difficult, not because it's a hard thing to do, but
because your table structure IS INCORRECT. You have a one-to-many
relationship embedded *in every record of your table*. Repeating
fields like Name1, Name2, Name3 and Name4 are a violation of the basic
principles of relational table design - and since your foundation is
built upon this base of sand, the house tottereth!

A normalized structure would have a many to many relationship using
*three* tables:

MainTable
RecordNo Primary Key
<information about the record as a whole>

Names
PersonName <don't use Name, it's a reserved word>
<any info about Luke as a person>
<actually I'd use a PersonID, not a name - you miight have two people
both named John, though I'm guessing your examples were hypothetical>

RecordNames
RecordNo <e.g. 1>
PersonName <e.g. Luke>
NameSeq <e.g. 1>

This would have four records for each record that you have now.

With your current structure, follow my suggestions, amplified in more
detail here:
Open Access. Select the Queries tab; create a new Query. Add your
table (I don't know what the name of the table is, you didn't tell me,
so I'll call it Table). Add it AGAIN, using the + icon on the toolbar.
Access will call it Table_1.
=[table_1].[Name2] OR [table_1].[Name3] OR [table_1].[Name4]

Just put this exact text string onto the Criteria line underneath the
field [Name1] from the table
.

That is, under Name2 put

=[table_1].[Name1] OR [table_1].[Name3] OR [table_1].[Name4]

and under Name3

=[table_1].[Name1] OR [table_1].[Name2] OR [table_1].[Name4]

I'll leave the criterion for Name4 as an exercise.

to prevent records< [table_1].[Record Number]

Just put this criterion on the Criteria line under [Record number] -
again, you didn't tell me the fieldname so I'm guessing.

Now open the query by clicking on the leftmost icon (looks like a
datasheet).
 
Back
Top