Hi,

Last post was very helpful so here I am again. I want to

1. Take a query results and put them in a record set.
2. Records in query are sorted by a Unique ID, and Phone number, then date
3. I need a script that will take that information and find duplicates, then choose the one with the longest minute within a specific time frame (7days)

Example
Unique ID Phone Date Duration
Heat1109 555-555-555 4/22/2010 0.5 (read this line save values)
Heat1109 555-555-555 4/22/2010 0.7 (compare and change)
Heat1109 555-555-555 4/24/2010 2.5 (compare and change)
Heat1109 555-555-555 4/31/2010 7.5 (compare don't change)
Duct1109 555-555-555 4/25/2010 3.5 (compare new record)

I would like the code to put the first line in a current array and test each subsequent line to see if

1. The Unique ID is the Same
2. The Phone number is the same
3. The minute duration is longer.
4. If the date is within 7 days
And if the next record meets the criteria replace the current value with the new values (except the date) and when finished put the record in another table, form, or query.

Please let me know if this is clear. I really do need help and learning this on my own is difficult. any comments, feedback, or assistance is greatly appreciated.

Or should i separate the arrays by the unique id.

Thanks,

Jakim

Jakim,

This will require writing VBA code.

See if I can help with the first step:

I would open a recordset where the query is the source

See help for: OpenRecordSet()

You could try nested queries. The last query picks up data from two joined queries, one of which has already identified the duplicates so it acts as a filter. These draw on another query which has pulled out the requisite records and added a unique ID (if it was not already in the original table).