question about running a comparison query

[joe_G]

Hi, I'm hoping someone can help me out with a problem I'm running into
with a query in access.
I'll just provide a test example so its easier to understand.
I have a table with four fields: Firstname, Lastname, date of birth
and social security number
The table contains over 50,000 records. In this table, I am trying to
run a query that will find an instance where the Firstname and
Lastname are equal values. For example, here is a sample of five
records:
In the example listed below, I would only want the value Joe Joe to be
displayed.

Joe Smith
John Smith
Bob Smith
Joe Joe
Bill Smith

Now, my friend is a programmer and he showed me a way to run this
query, but it is kind of complicated and hard to understand:
basically, he created an expression and used the following query
below:
Is there an easier way to get this data because I really dont
understand what is written below:

exp1: LTrim(RTrim([firstname]))

LTrim(RTrim([lastname]))

 

[tina]

well, i think you didn't post complete information, because the expressions
you posted will only trim the firstname and lastname fields - there's
nothing to compare the two, and in fact those two expressions couldn't be
used together as posted without erring.

suggest you add a calculated field to your query, as

AreEqual: Nz(Trim([Firstname]), "") = Nz(Trim([Lastname]), "")

for each record in the dataset, the calculated field with return True (the
first and last name values are equal) or False (the values are not equal).
just set criteria on the field, the way you would on any other field. to see
only records where the two fields are equal, set the criteria to True.

and btw, if you really have social security numbers in an Access database,
i'd say that's a bad idea. with all the identity theft issues in today's
world, social security numbers should be guarded zealously. and Access
cannot provide sufficient security to protect such sensitive data.

hth

 

[John W. Vinson]

Hi, I'm hoping someone can help me out with a problem I'm running into
with a query in access.
I'll just provide a test example so its easier to understand.
I have a table with four fields: Firstname, Lastname, date of birth
and social security number
The table contains over 50,000 records. In this table, I am trying to
run a query that will find an instance where the Firstname and
Lastname are equal values. For example, here is a sample of five
records:
In the example listed below, I would only want the value Joe Joe to be
displayed.

Joe Smith
John Smith
Bob Smith
Joe Joe
Bill Smith

Create a new Query based on the table.

On the Criteria line under Lastname put

=[Firstname]

The brackets are essential, otherwise it will find everyone with a last name
of "Firstname".

Open the query and you'll see the John John and Roberts Roberts records (some
few of which might actually be correct...)