extract duplicates in 2 fields with same 6 characters 2

[julius1]

Is there a way to set up criteria in a query that will locate duplicates of records based on 2 fields using the 1st 6 characters in both fields to find the match with?
Like I have 2 fields, one Aloc the other Zloc, between the 2 there are several that show the same 1st 6 characters. How can the criteria be set up to only return those that are the same between both fields but only counting up to the 1st 6 characters.

For example.
Aloc Zloc
WNVLMOXA WNVLMOXAK04
BASNMOXAK04 BASNMOXAH31

the 1st 6 characters are the same and that is what I am wanting to show in results. There are several variances, but the same theroy. I am lost trying to get just the duplicate results to populate based on 1st 6 characters.

julius.gazdag@centurytel.com
Im a Telco guy, I can get you where you want to go, but I can't guarantee it will work when you get there!

 

[r937]

[tt]select Aloc, Zloc
from yourtable
where left(Aloc,6) = left(Zloc,6)[/tt]

rudy

http://r937.com/

 

[PaulBricker]

SELECT tbl.Aloc, tbl.Zloc
FROM tbl
WHERE (((Left(tbl.Aloc,6))= Left([tbl].[Zloc],6)));

This should do it.

Paul

 

[AmritPaulSingh]

This should work

SELECT * from [Table1]
Where left(Table1.Aloc,6)=left(Table1.Zloc,6);

 

[julius1]

Do I just drop that into my criteria?

julius.gazdag@centurytel.com
Im a Telco guy, I can get you where you want to go, but I can't guarantee it will work when you get there!

 

[AmritPaulSingh]

Go to the sql view of the query and enter the code in

 

[julius1]

Here is my current statement. I know it's way off, but:
SELECT [A Z CLLI Compare].div, [A Z CLLI Compare].ckr, [A Z CLLI Compare].type, [A Z CLLI Compare].juris, [A Z CLLI Compare].aloc, [A Z CLLI Compare].zloc
FROM [A Z CLLI Compare];


julius.gazdag@centurytel.com
Im a Telco guy, I can get you where you want to go, but I can't guarantee it will work when you get there!

 

[AmritPaulSingh]

SELECT [A Z CLLI Compare].div, [A Z CLLI Compare].ckr, [A Z CLLI Compare].type, [A Z CLLI Compare].juris, [A Z CLLI Compare].aloc, [A Z CLLI Compare].zloc
FROM [A Z CLLI Compare]
WHERE left([A Z CLLI Compare].aloc,6)=left([A Z CLLI Compare].zloc,6);

 

[PaulBricker]

SELECT [A Z CLLI Compare].div, [A Z CLLI Compare].ckr, [A Z CLLI Compare].type, [A Z CLLI Compare].juris, [A Z CLLI Compare].aloc, [A Z CLLI Compare].zloc
FROM [A Z CLLI Compare]
WHERE Left([A Z CLLI Compare].Aloc,6) = Left([A Z CLLI Compare].Zloc,6);

Should do it.

Paul

 

[julius1]

Thank you very very much guys!!!!!!!!

julius.gazdag@centurytel.com
Im a Telco guy, I can get you where you want to go, but I can't guarantee it will work when you get there!