Comparing two access results against each other

I have two access tables I am trying to compare against each other.  The main table is the true source.  The input table is a table a receive from a third party.  The relationship between the two tables are the following fields:
AR_POL_ACCT_NUM
 CO_CMPY_CDE
This makes the record unique.  The problem I having i dont know how to create the comparison query in my access basic module to update the tbl_output when the records don't match.  I have deleted 25 records from tbl_input and change 5 others.  So when complete tbl_output should at least have 30 records in it.  So I know my third query needs to some type of outer join between tbl_main and and tbl_input.  Thats what I need help building in my access module.  I am attaching the database with tables and modules.
ComparisonTesting.accdb
centralmikeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GozrehCommented:
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));

Open in new window

0
GozrehCommented:
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.
0
centralmikeAuthor Commented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

GozrehCommented:
I added them in the module
ComparisonTesting.accdb
0
centralmikeAuthor Commented:
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.
0
GozrehCommented:
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.
0
centralmikeAuthor Commented:
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
0
GozrehCommented:
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));

Open in new window

0
GozrehCommented:
I forgot to add the file
ComparisonTesting.accdb
0
centralmikeAuthor Commented:
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.
0
GozrehCommented:
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.
0
GozrehCommented:
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));

Open in new window

0
centralmikeAuthor Commented:
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));
0
GozrehCommented:
   '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));")

Open in new window

0
Luke ChungPresidentCommented:
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.
0
centralmikeAuthor Commented:
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
0
GozrehCommented:
I added an IF Statement to check if the recordset has data.  "If Recordcount > 0 Then"
KBMG-RELATIONSHIP-STAGING.accdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
centralmikeAuthor Commented:
Thanks for all your help.  I am kinda new to coding in access basic and this gave me a better understanding of the language.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.