Page 1 of 3 123 LastLast
Results 1 to 15 of 44
  1. #1
    Join Date
    Oct 2010
    Posts
    49

    Unanswered: Two Tables 1.1m records each, will access handle a compare?

    Hi, I have two tables:

    Table A is a cut of customer records as at point in time A.
    Table B is a cut of customer records as at point in time B.

    I'd like to run a compare to show me if any of the data in the columns (There are 5 columns of data for each record) have changed, new records can be ignored.

    Will access be able to handle this volume of data, or should I look elsewhere, I'm new to access, although I know a bit of SQL, I wanted to be sure I wasn't wasting my time before learning how to write the query?

    I'm running Access on a standalone pc, Pentium 3 and only 1BG of memory.

    Any help greatly appreciated.

    Thanks

    Steven

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Access should be able to handle that, it may take time but I can't see why it shouldn't be able to

    there is one surefire way to find out

    ...do it and see what happens

    presuming you know something that is common to both tables (I'm guessing its going to be something like CustomerID), and presuming the column names are the same

    Code:
    select a.column2,a.column3,a.column4, b.column5, b.column2,b.column3,b.column4, b.column5 from thistable as A
    join thattable as B on B.customerID = a.customerID
    where a.column2 <> b.column2
    or a.column3 <> b.column3
    or a.column4 <> b.column4
    or a.column5 <> b.column5
    you may want to refine the query to identify customers which are new in the later table or not existing in the later table.. thats down to how you define your join
    Last edited by healdem; 10-06-10 at 05:05.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Unlikely it could cope with that much data, though I don't know if ACE is an improvement on JET. What version of Access? SQL Server Express would certainly be able to handle it.

    Quote Originally Posted by Ziggadebo View Post
    I'd like to run a compare to show me if any of the data in the columns (There are 5 columns of data for each record) have changed, new records can be ignored.
    Without specifying a key then every change does, in effect, create a new row.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    This isn't the final query, but will give you an idea how long the final query would take to run. Execute this and see if the performance is acceptable:
    Code:
    TABLE [Table A]
    UNION ALL
    TABLE [Table B]
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Oct 2010
    Posts
    49
    Thanks for the prompt repsonses, I'm using Access 2007, unfortunately I dont have SQL express at work.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Just noticed Healdem wrote a query. If there are no nulls in the table then his will be best. If there are nulls then building on a UNION query is better.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Ziggadebo View Post
    I dont have SQL express at work.
    It is free.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Oct 2010
    Posts
    49

    Smile

    Again, thanks for the replies, it's 15 years+ since last used access, so can't even rememebr how to write a query!

    I will find out and respond once, I've gotten the query working.

    FYI,

    There is a unique customer ID for each customer, so presumably I can use this as my primary key for both tables? some of the fields will contains NUllS.

    Thanks again

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    unless the columns are numeric I can't see why there should be an issue
    'all' you are doing is looking for differences
    you have a common column (heck to do a comparison, and form what you are saying they were the same table, there is no need to do a union all query.
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    In SQL if you write:
    Code:
    WHERE "something" <> NULL
    then nothing will get returned. NULLs do not equal anything and they also do not not equal anything.
    You'll therefore need to use the UNION query.
    Also, ensure that you explicitly define the primary keys for both tables. RDBMSs create B-Tree indexes to enforce primary keys and these indexes can be used by the engine to satisfy queries.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Mark, unless you wrap all the comparison columns in NZs then your query won't return rows where a NULL has changed to a non-NULL. I would put money on the performance of a UNION over your query with loads of NZs.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Oct 2010
    Posts
    49
    This isn't the final query, but will give you an idea how long the final query would take to run. Execute this and see if the performance is acceptable:

    Code:
    TABLE [Table A]
    UNION ALL
    TABLE [Table B]
    Ok, I've run this and it only took seconds, so am I best using the code healdem has suggested or using a UNION. Presumably if I use a union, I can then filter out records that aren't identical using my customer ID as the unique field?

  13. #13
    Join Date
    Oct 2010
    Posts
    49
    Ok, I've written this query:

    select a.client,a.Client_Name,a.Sort_Code, a.Bank_Account,a.Bank_AC_Status, b.client,b.Client_Name,b.Sort_Code, b.Bank_Account,b.Bank_AC_Status from Certbank as a
    join CertbankII as b on b.client = a.client
    where a.client_Name <> b.client_Name
    or a.Sort_Code <> b.Sort_Code
    or a.Bank_Account <> b.Bank_Account
    or a.Bank_AC_Status <> b.Bank_AC_Status
    Where Table 1 is CertBank and Table 2 is CertBankII, both tables have exactly the same column names.

    I keep getting syntax error in the from clause, any ideas? In sql I wouldn't need the 'as' when naming the table, have I got this right?

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ensure that the query completed. Access will return rows as soon as it can. I do not believe that it would have completed the query in seconds.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Change CertbankII to [CertbankII]. Also I think dumbass Access SQL requires the join order to be the same order as the tables so change
    on b.client = a.client
    to
    on a.client = b.client
    Also, you DO need the as
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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