how to find duplicate values of id field 1

[uscitizen]

hi,

i have a table called "ID Table" which can not have duplicate values of the person's medical record number (MR number), this field not being the last field of a composite PK.

I have created a query using the design wizard appearing below and I have added a 'Dummy' field to the table whose value is always '1'. the query was referenced in a macro which was linked to the 'after update' event of the 'MR Number' field BUT I WOULD LIKE IT TO BE ACTIVATED ONLY IF THERE ARE DUPLICATES FOUND.

SELECT [ID Table].[MR Number], [ID Table].[Last Name], [ID Table].[First Name], [ID Table].MI, [ID Table].Last_edited, [ID Table].Updated_by, [ID Table].Dummy
FROM [ID Table]
WHERE ((([ID Table].[MR Number]) In (SELECT [MR Number] FROM [ID Table] As Tmp GROUP BY [MR Number] HAVING Count(*)>1 )))
ORDER BY [ID Table].[MR Number];

IF THERE WERE SOME WAY THAT I COULD CONDITION THE MACRO TO TRIGGER THE DISPLAY OF THE QUERY WHEN THE SUM OF THE DUMMY COLUMN IS TWO OR GREATER??

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[cghoga]

SELECT [ID Table].[MR Number], Count([ID Table].[MR Number]) AS CountOfMRNumber
FROM [ID Table]
GROUP BY [ID Table].[MR Number]
HAVING (((Count([ID Table].[MR Number]))>1));

Hope this helps.

 

[uscitizen]

hi cghoga,

thanks for the input....what i think you're doing sql-wise is expressing a query that returns the MR_Number that has more than one record. What i think i needed was a listing of those when it is greater than 1 record and to ignore the query when it is just one record. the query would report on which last name, first name, mi had the same MR Number.

hope that makes better sense

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[uscitizen]

is there some way the value of the CountOfMRNumber could be used in a macro condition to actuate the code i originally drafted which would generate a list of the last name, first name and mi of records having the duplicated mr number?

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[PHV]

And what about the DCount function ?
If NZ(DCount("[MR Number]", "[ID Table]", "[MR Number]=" & Me![Name of MR Number Control]), 0) > 1 Then
MsgBox "Duplicate MR Number " & Me![Name of MR Number Control]
End If
If [MR Number] isn't numeric, you have to play with quotes:
If NZ(DCount("[MR Number]", "[ID Table]", "[MR Number]='" & Me![Name of MR Number Control] & "'"), 0) > 1 Then

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244