I don't know that this is necessarily the most efficient or best way to write
this, but it appears based on a quick test to work. My SQL looks like:
select * from testTbl
where left(keyFld, 9) in
(select distinct left(keyFld, 9) from testTbl
group by left(keyFld, 9)
having count(left(keyFld, 9)) > 1)
order by keyFld;
In general, my experience has been that when you are looking for duplicates
of something, you need to put the thing that is the duplicate in a table.
This is done courtesy of the nested select statement. The 'having' clause is
nested so that the table produced by the nested select only contains the keys
with duplicated left-most 9 characters.
--
Chaim
R Layedra said:
Hi John,
You're right on the money with what I want.
But, I still cannot come up with my desired result. I have created another
field where I have trimmed the APP_KEY to nine digits and that seems to work
but it's definately not what I wanted. Why is this simple (or so, it seems)
procedure so difficult to create?
Thanks for the help,
Rodolfo
John Spencer (MVP) said:
Close, but not quite. The user wanted to return the entire App_key (and
possibly other fields as well).
SELECT *
FROM WhatTable
WHERE Left([APP_KEY],9)
IN (SELECT Left([APP_KEY],9)
FROM WhatTable
GROUP BY Left([APP_KEY],9)
HAVING Count(Left([APP_KEY],9))>1)
Ofer wrote:
Try this query
SELECT Left([APP_KEY],9) AS Expr1
FROM MyTable
GROUP BY Left([APP_KEY],9)
HAVING Count(Left([APP_KEY],9))>1
:
I'm trying to find duplicates within a table where a field name called
APP_KEY is made up of 12 digits. However, I want the query to ONLY find
duplicates based on the first 9 digits: For example,
APP_KEY:
123456789001
123456789002
123456789003
123456789003
123456789003
I want the query to show me all FIVE records because they are all alike
within the first nine digits. I don't want just the last three!
Can anybody please help me? I've tried everything I can think of but my
access expertise is very limited.
