Results 1 to 3 of 3
  1. #1
    Dnphm is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2010
    Posts
    5

    Comparing two tables with query

    Hi,

    First let me say that I did not create this database but rather inherited from someone else.

    I have two tables, "tblProductCurrent" & "tblProducts" which store information about our products and their policy numbers. Both tables have the same fields, "PolicyNum", "ProductName". "tblProductCurrent" contains the most accurate information as it is updated.

    I am trying to compare the two tables to see if there is any difference in data. The "ProductName" is most likely the same for boh tables but the "PolicyNum" may be different. So, if the "PolicyNum" is different between the two tables, I would like the query to show it.



    Hope that makes sense! Thanks!

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    what you should do is quuery the table that has the most records and then write something like this:
    Code:
    select from table1

    where productname in 
    (select productname from table2)

    and 
    policynum <> 

    dlookup("policynum""table2""productname = '" productname "'"
    that's oracle type code, so use the brackets as required by access

  3. #3
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    If you are sure the productName of two table are match, you can query as following:

    select tblProductCurrent.*, tblProduct.* from tblProductCurrent inner join tblProduct on tblProductCurrent.productName=tblProduct.productNa me where tblProductCurrent.policynum<>tblProduct.policynum

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query Date without comparing time
    By Bosco in forum Queries
    Replies: 5
    Last Post: 06-28-2010, 03:59 PM
  2. Comparing fields in Access
    By flames8889 in forum Queries
    Replies: 2
    Last Post: 04-20-2010, 11:55 AM
  3. Replies: 0
    Last Post: 03-03-2010, 08:24 PM
  4. Help On Comparing Quantity in Tables
    By rochy81 in forum Access
    Replies: 2
    Last Post: 05-29-2009, 10:20 AM
  5. Comparing between fields
    By Dashin in forum Queries
    Replies: 0
    Last Post: 02-13-2009, 08:38 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums