Check for Duplicate employee

[dhborchardt]

I need a query that will Grab the first name and middle initial, make it one field (I know how to do this part), and then check to see if there are duplicate values. For example if it found two John J entries, it would warn with a popup window.

Thanks

 

[rorymurray]

Try this query:

select first_name & left(middle_name, 1)
from table
having count(first_name & left(middle_name, 1))>1

Then it is just a matter of running the query when you need to do the check.

Rory

 

[dhborchardt]

Thanks Rory
I was trying to achieve this in two queries. Its great to see it done in one. What is the purpose of the "1" in
"Left(MiddleI,1)"
I am trying to work this into my query and I keep getting the error "Cant have aggregate function in WHeRE clause ((Count([FirstName] & Left([MiddleI],1)))>1)"

 

[rorymurray]

The purpose of the "1" in the left(middle, 1) is to say that you want to look at the first 1 character on the left of the variable "middle". So say instead you wanted to look at the left 2 characters of a variable containing the word "hello", you would do a left(variable, 2), which would return "he".

As far as working out what the problem with the other query is, I would have to see the full SQL of it, but my guess is that you have put the Count() part in a WHERE clause. If you want to use count in some sort of condition you need to use the HAVING clause. For example, if you were trying to find everybody that shares a surname with somebody else in the table(ie Count(surname>1)), and has a middle name of Peter:

SELECT firstname
FROM nametable
WHERE middlename='Peter'
GROUP BY surname
HAVING Count(surname)>1;

If you don't really understand this example, just have a read up on the GROUP BY and HAVING in any SQL reference.

Hope this helps.

Rory