Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2004
    Posts
    2

    Unanswered: Newbie Question about Comparing tables

    For Access 2000
    The general problem is that I have two lists that contain phone numbers. We can call them "old" and "new". I need to generate another list from new that only contains phone numbers that are not already in old and return the whole row. Initially this would be with lists around 4392 and 10000 in size.

    I would use the following SQL:
    Select * INTO Cleaned
    From New
    Where (((New.Phone) Not In (Select (Old.Phone) From Old)))

    This would take about 4 minutes and worked just as expected. Now the lists are 170000 and 16000 items big. This would take four hours at least I think. I just started it. Is there a more efficient way to do this perhaps along these lines. I do not know VB yet.
    Please excuse the syntax. I remeber very little from my collegee C and Pascal courses and this is the only way I could figure to express the idea.


    Sort both lists in ascending order by the phone field.
    Start with the first lines of both old and new.

    While (New.Phone<>EndofTable)
    { If ((New.Phone<Old.Phone) or (Old.Phone=EndofTable))
    {Add New.Phone's Row to Cleaned
    Update New.Phone to next row
    Return}
    If (New.Phone>Old.Phone)
    {Update Old.Phone to next row
    Return}
    If (New.Phone=Old.Phone)
    {Update New.Phone to next row
    Update Old.Phone to next row
    Return}
    }

    I think I have the right idea but I have no idea how to implement it not knowing VB. Is there a way to crawl the lists or something even simpler?

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    The subquery in your where clause has to be executed for each row, that's what's taking so long. If you were to left join the tables together and select only records where old is null, then you would only reference the "old" table ONCE and save yourself a bunch of processing.
    Last edited by Teddy; 09-28-04 at 17:10.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Sep 2004
    Posts
    2

    Many thanks.

    Many thanks. I do tend to over complicate things.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    In all fairness, a subquery was my first thought to...

    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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