Results 1 to 3 of 3
  1. #1
    Join Date
    May 2010
    Posts
    3

    Unanswered: Creating a RecordSet from a Query

    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
    Last edited by Shoback; 05-21-10 at 17:34. Reason: need more tags and accidentally hit enter

  2. #2
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by Shoback View Post
    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:

    1. Take a query results and put them in a record set.
    I would open a recordset where the query is the source

    See help for: OpenRecordSet()
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    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).

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •