check for duplicates

[mabaker]

I have a table that has a field called "telephone" and another field called "Creation_date"
I want to be able to check first to see if there are duplicate phone numbers and if so I want to only show the record with the latest creation_date on my report.

Any ideas?

 

[FoFa]

Group by phone number select max(creationDate)

 

[dcx693]

If you put also put Count of your grouped telephone numbers in your totals query, then you see if you have duplicates.

 

[mabaker]

Would I put it in a query or in VB code in an event?
(The group by)

 

[dcx693]

The grouping is done in the query. In the query design view, go to the View menu and choose Totals.

 

[mabaker]

I get an error "You can't calculate totals on the asterisk (*)" I have selected all of the fields.

 

[Jacob Mathai]

pls try this.
It will show the ones with duplicates.


SELECT telno, Count(*) AS count1
FROM mytable GROUP BY telno
HAVING count(*) > 1

 

[mabaker]

Works Great Jacob!

Thank you very much!