Access Match Query

[bigmerf]

I'm looking to run a "Match" query that checks for matches on a customer name field within one specific table. My customer table has about 50,000 records and I want to find all those customer names that match based on the first 10 characters.

I can run the default "query wizard" to create a TOTAL MATCH QUERY, but how would I run a query that would display matches on customer name based on only the first 10 characters?

Any help would be great.

Thanks!

 

[PWise]

Select * from table1
inner join table2 on table1.fieldname=left(table2.fieldname,10)

 

[bigmerf]

This not exactly what I was looking for. I only have 1 table, and want to display all duplicate customer names based on the first 10 characters. I don't have multiple tables, so a join would not be the correct way to query.

Is there a way to query based off of one table to show dups on customer name based on any number of characters?

 

[PWise]

Select * from table1
inner join [COLOR=red]table1 as table2[/color] on table1.fieldname=left(table2.fieldname,10)

 

[PWise]

or

select count(*),left(fieldname,10)
from tablename
group by left(fieldname,10)

 

[bigmerf]

No, that doesn't seem to be correct. I have 50,00 records in my table but when running the query based on the SQL logic you disclosed, I'm getting upwards around 85,000 duplicates.......

I'm not sure I understand the purpose of the join and creating another table to compare the records. Isn't there a SELECT statement that can be run using the "left" function without using a join?

This is basic SQL logic that I have to show all duplicates with no special number of matching characters:

In (SELECT [CustomerName] FROM [CUSTOMERS] As Tmp GROUP BY [CustomerName] HAVING Count(*)>1 )

I've tried adding the "Left" function here but it doesn't seem to work.

Anyone have ideas?

 

[bigmerf]

The problem with the "GROUP BY" query is that it will show all customers, and not just duplicates. So the count function listed will show several customers as just "1".

I'll keep playing.....

 

[PHV]

SELECT Left(fieldname,10) AS DupName, Count(*) AS CountOfDup
FROM tablename
GROUP BY Left(fieldname,10)
HAVING Count(*)>1

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