Removing Duplicates based on criteria 1

[newguy86]

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

 

[SkipVought]

Hi,

???

What is this?

It makes no sense!

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

 

[newguy86]

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

 

[dhookom]

Please use the TGML Pre tag to display your records:

[pre]
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[/pre]

Duane
Hook'D on Access
MS Access MVP

 

[newguy86]

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

 

[dhookom]

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

 

[newguy86]

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