Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Posts
    311

    Unanswered: reconciling 2 rs

    I have 2 recordsets both have the same columns

    i have sorted by date, amount, vendor, salesperson
    this combo will be unique

    the 1st rs named old is older then the 2nd rs named New

    i am trying to find records that where in Old and still in New. And find records that were in old but not in new. and finally recs that are in new but were not in old ( reconcile the 2 rs)

    i was thinking
    dim Old as rs
    dim New as rs

    if Old("date") = New("date") then
    if Old("Amount") = New("Amount") then
    if Old("Vendor") = New("Vendor") then
    if Old("SalesPerson") = New("SalesPerson") then
    'this should be a match so:
    New("Prior") = true ' the record was in the old rs

    but i get lost on the move next loging and the else parts anyhelp or better idea apreciated.


    thanks


    M~

  2. #2
    Join Date
    Nov 2003
    Posts
    267
    Why not just run the SQL to figure out your recordset

    In Both

    SELECT [New].[Date], [New].[Amount], [New].[Vendor], [New].[Salesperson]
    FROM New INNER JOIN Old ON ([New].[Salesperson]=[Old].[Salesperson]) AND ([New].[Vendor]=[Old].[Vendor]) AND ([New].[Amount]=[Old].[Amount]) AND ([New].[Date]=[Old].[Date]);


    In Old Only
    SELECT Old.Date, Old.Amount, Old.Vendor, Old.Salesperson
    FROM New RIGHT JOIN Old ON (New.Date = Old.Date) AND (New.Amount = Old.Amount) AND (New.Vendor = Old.Vendor) AND (New.Salesperson = Old.Salesperson)
    WHERE (((New.Salesperson) Is Null));

    In New Only
    SELECT [New].[Date], [New].[Amount], [New].[Vendor], [New].[Salesperson]
    FROM New LEFT JOIN Old ON ([New].[Salesperson]=[Old].[Salesperson]) AND ([New].[Vendor]=[Old].[Vendor]) AND ([New].[Amount]=[Old].[Amount]) AND ([New].[Date]=[Old].[Date])
    WHERE ((([Old].[Salesperson]) Is Null));


    The do what you want with each recordset. This way you don't have to use code to determine which category it falls in

    S-

Posting Permissions

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