Find duplicats from one field and LIKE another field 1

[jadams0173]

I used the duplicate query wizard to find duplicates based on field1. I've tried to modify it with no luck to find duplicates in field1 and like field2. Example


field1 field2
1234 T00
1234 T0A
1234 B00
5432 T00
5432 B0A
5432 B00

The records I would like to return are
1234 T00
1234 T0A
5432 B0A
5432 B00

Something like Where Field1 Count(*)>1 and Field2 Count(Like Left(field2) & '*' >1).
Hope that makes some sense. Can this be done in one query?

I tried to have patience but it took to long! -DW

 

[PHV]

Perhaps something like this ?
SELECT A.field1, A.field2
FROM yourTable AS A INNER JOIN (
SELECT field1, Left(field2,1) AS f2 FROM yourTable
GROUP BY field1, Left(field2,1) HAVING Count(*)>1
) AS B ON A.field1=B.field1 AND Left(field2,1)=B.f2

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jadams0173]

Hey PHV,

Thanks for the reply. I get an error "Join Expression Not Supported" on the Red line

SELECT A.field1, A.field2
FROM yourTable AS A INNER JOIN (
SELECT field1, Left(field2,1) AS f2 FROM yourTable
GROUP BY field1, Left(field2,1) HAVING Count(*)>1
) AS B ON A.field1=B.field1 AND [red]Left(field2,1)=B.f2 [/red]

I tried to have patience but it took to long! -DW

 

[PHV]

SELECT A.field1, A.field2
FROM yourTable AS A INNER JOIN (
SELECT field1, Left(field2,1) AS f2 FROM yourTable
GROUP BY field1, Left(field2,1) HAVING Count(*)>1
) AS B ON A.field1=B.field1
WHERE Left(field2,1)=B.f2

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jadams0173]

Thanks PHV. This is really close to what I need. I'm going to try to get it from here as I need to add some addtional criteria and fields. This IS exactly what I asked for and I now have a great starting point!! Many Thanks.

ps. My email notification didn't work either time you replied.....

I tried to have patience but it took to long! -DW