Solved

I want to eliminate Duplicate Records  with minimum time

Posted on 2014-09-17
4
117 Views
Last Modified: 2014-09-17
I am including a sample database.  Trying to query to eliminate Duplicate Records with min time.  
I tried just the two fields---[ID]&[TakenTime] and works but add other fields it doesn’t.

Table:DupRecords
ID      MinOfCMMT_ID      TakenTime
6100174005      174005      9:12:00 AM
6100174005      174018      9:26:00 AM
6100174012      174012      9:18:00 AM
6100174012      174019      9:27:00 AM



Query1
SELECT DupRecords.ID, DupRecords.MinOfCMMT_ID, Max(DupRecords.TakenTime) AS MaxOfTakenTime
FROM DupRecords
GROUP BY DupRecords.ID, DupRecords.MinOfCMMT_ID;
I would appreciate any help!
DupRecords.mdb
0
Comment
Question by:ca1358
  • 2
4 Comments
 
LVL 13

Expert Comment

by:AielloJ
Comment Utility
ca1358:

Can you provide an example of what you expect the results to be?

Best regards,

AielloJ
0
 

Author Comment

by:ca1358
Comment Utility
6100174005      174018      9:26:00 AM
6100174012      174019      9:27:00 AM
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
try this query


SELECT T.*
FROM DupRecords AS T
INNER JOIN
(SELECT ID, Max(TakenTime) AS MaxOfTakenTime
FROM DupRecords
GROUP BY ID) As T1
ON T.ID=T1.ID and T.TakenTime=T1.MaxOfTakenTime
0
 

Author Closing Comment

by:ca1358
Comment Utility
Awesome, Thank you!!
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now