Comparing two tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Table 1: List of Accounts that are used in a Crosstab query
Acct 1001 Witget Income
Acct 2001 Witget Shipping Expenses

Table 2: Monthly Store Income and Expenses listed by account numbers
Store 001 Acct 1001 $50,000
Store 001 Acct 2001 $ 1,000
Store 001 Acct 3333 $ 800

Sometimes, new Account Numbers appear in the Monthly Store data that are not
in the Main List of Accounts table. At present, I run a query using both
tables in a join to determine if there are new account numbers. Then I do a
table append query to add any new account numbers to the List of Accounts
table.

Is there a way to progamatically compare the two tables and pull in the new
account numbers to the Main List of Accounts table? I would appreciate a
code as well as a logic explanation.
 
I have assumed the following;

tblAccount
AccountID*
AccountDescription

tblStore
StoreID*
AccountID*
Amount

I suspect that you will have more fields and probably more tables in the
database but for symplicity i have assumed only these 2 tables and fields.

You can create an append query that looks in both tables and determines
which account is present in tblStore but not in tblAccount, e.g.;

INSERT INTO tblAccount ( AccountID )
SELECT tblStore.AccountID
FROM tblAccount RIGHT JOIN tblStore ON tblAccount.AccountID =
tblStore.AccountID
WHERE tblAccount.AccountID Is Null;

This query will find any missing accounts in tblStore and append them too
tblAccount.

To run the query from code you can use the execute method of the current
database e.g.

CurrentDb.Execute "qryQueryName", dbFailOnError

Of course the account description will be missing, unless this is available
in your Table 2 in which case you you could amend the query to populate the
account description, or you will have to enter this manually into the table.

Hope this helps.
 
Back
Top