INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Removing Duplicates based on criteria

Removing Duplicates based on criteria

(OP)
What I'm trying to accomplish is removing duplicate records from a query based on the date/time stamp of the record.

Now for a little backstory, we have a table of data that is a log of every status change for an order and the date/time that the status change occured. We are trying to report off of this table to get turnaround times for processing orders.

Here's the table structure:

tblRAW
ID
ContractNo
Name
StatusDesc
Username
UpdateDateTime
Version

Now so far I have created several queries to seperate out the date/time stamps based on the StatusDesc field. We did this so that we could bring the Date/Time stamps back together into a single table/query and have seperate columns for each StatusDesc (Submitted, Accepted, Revised, etc.) which will be grouped on the ContractNo and Version fields.

The issue we have is that because some orders get submitted/revised and then get recalled before they can be processed only to be resubmitted/revised later, the log table ends up having duplicate records because the original new order or revision that was submitted never got processed but still generated a date/time stamp for it.

So what I'm trying to figure out is how to remove those duplicate records but I need to remove the earlier records rather than the later records, which is how Access seems to automatically handle duplicates.

Here's an example of the data that I'm seeing:

ContractNo
Version
Name
StatusDesc
Username
UpdateDateTime
11381403
0
NCC
Submitted
jodoe
2/6/2015 10:31:53 AM
11381403
0
NCC
Submitted
jodoe
2/6/2015 10:40:21 AM
11381403
0
NCC
Accepted
jadoe
2/6/2015 10:50:45 AM
11381403
1
NCC
Revised
jodoe
2/7/2015 11:44:59 AM
11381403
1
NCC
Rev Accepted
jadoe
2/7/2015 12:12:09 AM
11381403
2
NCC
Revised
jodoe
2/12/2015 11:13:49 AM
11381403
2
NCC
Revised
jodoe
2/12/2015 11:20:15 AM
11381403
2
NCC
Rev Accepted
jadoe
2/12/2015 11:32:12 AM

Here's an example of the data with the duplicates removed:

ContractNo
Version
Name
StatusDesc
Username
UpdateDateTime
11381403
0
NCC
Submitted
jodoe
2/6/2015 10:40:21 AM
11381403
0
NCC
Accepted
jadoe
2/6/2015 10:50:45 AM
11381403
1
NCC
Revised
jodoe
2/7/2015 11:44:59 AM
11381403
1
NCC
Rev Accepted
jadoe
2/7/2015 12:12:09 AM
11381403
2
NCC
Revised
jodoe
2/12/2015 11:20:15 AM
11381403
2
NCC
Rev Accepted
jadoe
2/12/2015 11:32:12 AM

So let me know if you have any questions and any help will be greatly appreciated.

Travis
Charter Media

RE: Removing Duplicates based on criteria

Hi,

???

What is this?

It makes no sense!

I've never seen a column of data like this all mixed up!

RE: Removing Duplicates based on criteria

(OP)
I tried to space out the data by tabbing out. I guess that's how the system processed it.

Let me try that again, here's an example of the data that I'm seeing:

ContractNo...Version...Name...StatusDesc.......Username...UpdateDateTime
11381403.....0............NCC.....Submitted.........jodoe.........2/6/2015 10:31:53 AM
11381403.....0............NCC.....Submitted.........jodoe.........2/6/2015 10:40:21 AM
11381403.....0............NCC.....Accepted..........jadoe.........2/6/2015 10:50:45 AM
11381403.....1............NCC.....Revised............jodoe........2/7/2015 11:44:59 AM
11381403.....1............NCC.....Rev Accepted...jadoe........2/7/2015 12:12:09 AM
11381403.....2............NCC.....Revised...........jodoe.........2/12/2015 11:13:49 AM
11381403.....2............NCC.....Revised...........jodoe.........2/12/2015 11:20:15 AM
11381403.....2............NCC.....Rev Accepted...jadoe.........2/12/2015 11:32:12 AM

Here's an example of the data with the duplicates removed:

ContractNo...Version...Name...StatusDesc.......Username...UpdateDateTime
11381403.....0............NCC.....Submitted.........jodoe.........2/6/2015 10:40:21 AM
11381403.....0............NCC.....Accepted..........jadoe.........2/6/2015 10:50:45 AM
11381403.....1............NCC.....Revised...........jodoe.........2/7/2015 11:44:59 AM
11381403.....1............NCC.....Rev Accepted...jadoe.........2/7/2015 12:12:09 AM
11381403.....2............NCC.....Revised...........jodoe.........2/12/2015 11:20:15 AM
11381403.....2............NCC.....Rev Accepted...jadoe.........2/12/2015 11:32:12 AM

Travis
Charter Media

RE: Removing Duplicates based on criteria

Please use the TGML Pre tag to display your records:

ContractNo   Version   Name    StatusDesc     Username   UpdateDateTime
11381403     0         NCC     Submitted      jodoe      2/6/2015 10:31:53 AM
11381403     0         NCC     Submitted      jodoe      2/6/2015 10:40:21 AM
11381403     0         NCC     Accepted       jadoe      2/6/2015 10:50:45 AM
11381403     1         NCC     Revised        jodoe      2/7/2015 11:44:59 AM
11381403     1         NCC     Rev Accepted   jadoe      2/7/2015 12:12:09 AM
11381403     2         NCC     Revised        jodoe      2/12/2015 11:13:49 AM
11381403     2         NCC     Revised        jodoe      2/12/2015 11:20:15 AM
11381403     2         NCC     Rev Accepted   jadoe      2/12/2015 11:32:12 AM

Here's an example of the data with the duplicates removed:

ContractNo   Version   Name    StatusDesc     Username   UpdateDateTime
11381403     0         NCC     Submitted      jodoe      2/6/2015 10:40:21 AM
11381403     0         NCC     Accepted       jadoe      2/6/2015 10:50:45 AM
11381403     1         NCC     Revised        jodoe      2/7/2015 11:44:59 AM
11381403     1         NCC     Rev Accepted   jadoe      2/7/2015 12:12:09 AM
11381403     2         NCC     Revised        jodoe      2/12/2015 11:20:15 AM
11381403     2         NCC     Rev Accepted   jadoe      2/12/2015 11:32:12 AM 

Duane
Hook'D on Access
MS Access MVP

RE: Removing Duplicates based on criteria

(OP)
dhookom,
Thank you! I'll be sure to keep that in mind next time I need to post a sample of records.

Any suggestions on resolving my duplicate records issue?

Travis
Charter Media

RE: Removing Duplicates based on criteria

This looks like a simple totals query that groups by all fields/columns other than the UpdateDateTime which should use Max.

Duane
Hook'D on Access
MS Access MVP

RE: Removing Duplicates based on criteria

(OP)
I must have had a massive lapse in memory because I didn't even think of trying that but that's exactly what I was looking for.

Thank you.

Travis
Charter Media

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close