Comparing two access results against each other

To get all new record's you should use a Right Join from tblMain to tblInput.

SELECT tbl_Main.*
FROM tbl_Input RIGHT JOIN tbl_Main ON (tbl_Input.CO_CMPY_CDE = tbl_Main.CO_CMPY_CDE) AND (tbl_Input.AR_POL_ACCT_NUM = tbl_Main.AR_POL_ACCT_NUM)
WHERE (((tbl_Input.CO_CMPY_CDE) Is Null));

Select allOpen in new window

To get the changes, you will need to add a field ModifyTimeStamp, and it should be updated after each change on this record, then you can match the tables by if timestamp is different.

I get the outer join query statement.  But i need this in the access module.  Also I am not getting the 5 rows that don't match.  Its not update query.  It's a query that is comparing recordsets to each other.  I need the records that dont match from tbl_main inserted into the tbl_output table.

I added them in the module
ComparisonTesting.accdb

I think we are almost there.  Just a couple of questions.  Can we not update table tbl_output in the code module I will need to create a report of this table after the comparison is complete? Second questions in the record change query in the module you have a join on AO2_ADMIN_SYS_CDE. The only two columns that want change are AR_POL_ACCT_NUM  and CO_CMPY_CDE, so can we eleminate there where statement.  Two queries return the correct results I am looking for.

1) Why not making a report on the tbl_output.

2) The Join on AO2_ADMIN_SYS_CDE in the Where statement is to check if they are different.

Question 1 tbl_Output never gets updated in the module

Question 2 - Should I add the other columns in the where statement that can change.
example
 "WHERE ((([tbl_Main_orig]![AO2_ADMIN_SYS_CDE]=[tbl_Input]![AO2_ADMIN_SYS_CDE])=False));")

          [tbl_main  rst2]![SI_PRODUCTION_NUM] =[tbl_Input]![SI_PRODUCTION_NUM])=False
          [tbl_main  rst2]![AO2_ADMIN_SYS_CDE] = [tbl_Input]![AO2_ADMIN_SYS_CDE])=False
          [tbl_main  rst2]![AO2_OPERATOR_ID] =  [tbl_Input]![AO2_OPERATOR_ID])=False
          [tbl_main  rst2]![AO2_CURR_TME_STAMP] =  [tbl_Input]![AO2_CURR_TME_STAMP])=False
          [tbl_main  rst2]![AO2_SPLIT_PCT] =  [tbl_Input]![AO2_SPLIT_PCT])=False
          [tbl_main  rst2]![FP9_ASGN_NUM] = [tbl_Input]![FP9_ASGN_NUM)=False

Answer 1 - I update the module it should add new records in tbl_Output

Answer 2 - you can add to the Where statement all fields that can be changeable, with the OR  statement:

SELECT tbl_Main_orig.*
FROM tbl_Input INNER JOIN tbl_Main_orig ON (tbl_Input.SI_PRODUCTION_NUM = tbl_Main_orig.SI_PRODUCTION_NUM) AND (tbl_Input.AR_POL_ACCT_NUM = tbl_Main_orig.AR_POL_ACCT_NUM) AND (tbl_Input.CO_CMPY_CDE = tbl_Main_orig.CO_CMPY_CDE)
WHERE ((([tbl_Main_orig]![AO2_ADMIN_SYS_CDE]=[tbl_Input]![AO2_ADMIN_SYS_CDE])=False)) 
OR ((([tbl_Main_orig]![SI_PRODUCTION_NUM]=[tbl_Input]![SI_PRODUCTION_NUM])=False));

Select allOpen in new window

I forgot to add the file
ComparisonTesting.accdb

I would have expected table tbl_Output to have 31 records when the module complete.  It looks like the "delete statement might be in the wrong place.   We have 26 new records and 5 change records.  When you run the module you only get 27 records.  Also I couldn't get the "Or" statement to work with the additional fields.

i see in your data few dublex AR_POL_ACCT_NUM, like
BC7009106  
BC7009110  
BC7009122
BC7000028
so from 31 you will only get 27 records.

Your tables should have a unique number.

What's was your problem with the OR statement

SELECT tbl_Main_orig.*
FROM tbl_Input INNER JOIN tbl_Main_orig ON (tbl_Input.SI_PRODUCTION_NUM = tbl_Main_orig.SI_PRODUCTION_NUM) AND (tbl_Input.AR_POL_ACCT_NUM = tbl_Main_orig.AR_POL_ACCT_NUM) AND (tbl_Input.CO_CMPY_CDE = tbl_Main_orig.CO_CMPY_CDE)
WHERE ((([tbl_Main_orig]![AO2_ADMIN_SYS_CDE]=[tbl_Input]![AO2_ADMIN_SYS_CDE])=False)) 
OR ((([tbl_Main_orig]![SI_PRODUCTION_NUM]=[tbl_Input]![SI_PRODUCTION_NUM])=False)) 
OR ((([tbl_Main_orig]![AO2_ADMIN_SYS_CDE]=[tbl_Input]![AO2_ADMIN_SYS_CDE])=False)) 
OR ((([tbl_Main_orig]![AO2_OPERATOR_ID]=[tbl_Input]![AO2_OPERATOR_ID])=False)) 
OR ((([tbl_Main_orig]![AO2_CURR_TME_STAMP]=[tbl_Input]![AO2_CURR_TME_STAMP])=False)) 
OR ((([tbl_Main_orig]![AO2_SPLIT_PCT]=[tbl_Input]![AO2_SPLIT_PCT])=False)) 
OR ((([tbl_Main_orig]![FP9_ASGN_NUM]=[tbl_Input]![FP9_ASGN_NUM])=False));

Select allOpen in new window

when i put your code in record change statement.  All the lines turn red.  I tried putting in a continuation line _ and the code remains red.
here is the code

   rst.Close
   
   'Records Changed
   Set rst = CurrentDb.OpenRecordset("SELECT tbl_Main_orig.* " & _
            "FROM tbl_Input INNER JOIN tbl_Main_orig ON (tbl_Input.SI_PRODUCTION_NUM = tbl_Main_orig.SI_PRODUCTION_NUM) AND (tbl_Input.AR_POL_ACCT_NUM = tbl_Main_orig.AR_POL_ACCT_NUM) AND (tbl_Input.CO_CMPY_CDE = tbl_Main_orig.CO_CMPY_CDE) " & _
           WHERE ((([tbl_Main_orig]![AO2_ADMIN_SYS_CDE]=[tbl_Input]![AO2_ADMIN_SYS_CDE])= False)) _
                OR ((([tbl_Main_orig]![SI_PRODUCTION_NUM]=[tbl_Input]![SI_PRODUCTION_NUM])= False)) _
                OR ((([tbl_Main_orig]![AO2_ADMIN_SYS_CDE]=[tbl_Input]![AO2_ADMIN_SYS_CDE])= False)) _
                OR ((([tbl_Main_orig]![AO2_OPERATOR_ID]=[tbl_Input]![AO2_OPERATOR_ID])= False)) _
                OR ((([tbl_Main_orig]![AO2_CURR_TME_STAMP]=[tbl_Input]![AO2_CURR_TME_STAMP])= False)) _
                OR ((([tbl_Main_orig]![AO2_SPLIT_PCT]=[tbl_Input]![AO2_SPLIT_PCT])= False)) _
                OR ((([tbl_Main_orig]![FP9_ASGN_NUM]=[tbl_Input]![FP9_ASGN_NUM])= False));

   'Records Changed
   Set rst = CurrentDb.OpenRecordset("SELECT tbl_Main_orig.* " & _
            "FROM tbl_Input INNER JOIN tbl_Main_orig ON (tbl_Input.SI_PRODUCTION_NUM = tbl_Main_orig.SI_PRODUCTION_NUM) AND (tbl_Input.AR_POL_ACCT_NUM = tbl_Main_orig.AR_POL_ACCT_NUM) AND (tbl_Input.CO_CMPY_CDE = tbl_Main_orig.CO_CMPY_CDE) " & _
            "WHERE ((([tbl_Main_orig]![AO2_ADMIN_SYS_CDE]=[tbl_Input]![AO2_ADMIN_SYS_CDE])=False)) " & _
            "OR ((([tbl_Main_orig]![SI_PRODUCTION_NUM]=[tbl_Input]![SI_PRODUCTION_NUM])=False))  " & _
            "OR ((([tbl_Main_orig]![AO2_ADMIN_SYS_CDE]=[tbl_Input]![AO2_ADMIN_SYS_CDE])=False))  " & _
            "OR ((([tbl_Main_orig]![AO2_OPERATOR_ID]=[tbl_Input]![AO2_OPERATOR_ID])=False))  " & _
            "OR ((([tbl_Main_orig]![AO2_CURR_TME_STAMP]=[tbl_Input]![AO2_CURR_TME_STAMP])=False))  " & _
            "OR ((([tbl_Main_orig]![AO2_SPLIT_PCT]=[tbl_Input]![AO2_SPLIT_PCT])=False))  " & _
            "OR ((([tbl_Main_orig]![FP9_ASGN_NUM]=[tbl_Input]![FP9_ASGN_NUM])=False));")

Select allOpen in new window

If you are interested in a commercial solution, our Total Access Detective product compares any two Access databases or two objects in one database for differences. This includes differences in records between tables where keyed fields identify records to compare.

In your case. if you make the two fields that are unique values your primary key, the two tables can be compared for differences. Total Access Detective also offers a feature to combine the differences between two tables into a new table. All without having to write any code or queries: http://www.fmsinc.com/MicrosoftAccess/difference/data/combine-tables.asp

Hope this helps.

one final question for you.  What happens when you have no new records or no records that change?  I get a error code 3021.  I am not sure how to put error code checking in the module.  if you can add that final piece to the module or tell me how to put in that error code handlings.  I am attaching the database with a lot of changes.  But the module that needs the error code is mod_KBMG_Staging
KBMG-RELATIONSHIP-STAGING.accdb

Thanks for all your help.  I am kinda new to coding in access basic and this gave me a better understanding of the language.