Comapring 2 Fields

[report2000]

I have two tables.

Both tables have same field names
FirstName, LastName

I would like to compare values in both tables based on the first 3 charaters of FirstName or LastName

 

[Darrylles]

Hi,

When you say 'compare' what exactly do you want to do?

I.E. Do you want to display ALL fields from BOTH tables where rows match on the first 3 chars of FirstName and LastName?

If so:

Tables are called Table1 and Table2.

Create the following queries (selecting additional fields that you wish to see in both queries)....

Query qryTab1:
[tt]
SELECT Left([FirstName],3) AS Tab1FN, Left([LastName],3) AS Tab1LN
FROM Table1;
[/tt]

Query qryTab1:
[tt]
SELECT Left([FirstName],3) AS Tab2FN, Left([LastName],3) AS Tab2LN
FROM Table2;
[/tt]

Create a third query to compare the 2 queries (selecting additional fields that you wish to see from both tables)....
[tt]
SELECT qryTab1.Tab1FN, qryTab1.Tab1LN, qryTab2.Tab2FN, qryTab2.Tab2LN
FROM qryTab1 INNER JOIN qryTab2 ON (qryTab1.Tab1LN = qryTab2.Tab2LN) AND (qryTab1.Tab1FN = qryTab2.Tab2FN);
[/tt]

A SQL expert will do it in one, but this will work.

ATB

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[PHV]

Something like this (SQL code) ?
SELECT A.*, B.*
FROM Table1 AS A, Table2 AS B
WHERE Left(A.FirstName,3)=Left(B.FirstName,3) OR Left(A.LastName,3)=Left(B.LastName,3)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Darrylles]

Roger that PHV,

Tut

ATB

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.