I have a db of 1 Company's Trial Balances. I have a unique problem (at least I think I do) in that for each GL Account number I must keep a history of what the original Account Title was when it was imported from Excel into the Access db and must maintain it for govt reporting requirements. Multiple periods of Trial Balance ending balances will be maintained at all times in the db.
So in order to do this I created 3 tables.
1st table has the account number (which is a Text field due to account mask of XXX-XX-XXX-XX-XX) called ClientAcctNbrs
2nd table has the Account Description and an Effective Date for the End of Month that account title began being used for the Account Number. It is called AcctNbrDescriptions
3rd table is a many to many relationship for matching up the Account Number with the Account Description. I called it Acct_Nbr_Description_Effec
Maybe there is a better way to do it but I couldn't figure one out....so this is what I have.
When I import the new period's ending trial balance I bring it into a TempClientTrialBalance table first to validate info before sending the final to the permanant ClientTrialBalance table.
Now then my question.
When I import it I want to be able to Compare the Account Number field of the Temp table with the permanant ClientAcctNbr table to see if there are any new Account numbers being used and do the same for AcctNbrDescriptions. Then add the Accounts, Descriptions and the Many to Many relationship.
When I look at query's though the only option I see is to display when they are equal. Is there a way to display only the NON equal?
Following is my SQL thus far:
SELECT TempClientTrialBalance.TempClientTrialBalID, TempClientTrialBalance.Description, TempClientTrialBalance.[Cost Period Ending], TempClientTrialBalance.ClientAcctNbr, ClientAcctNbrs.ClientAcctNbr, AcctNbrDescriptions.Description
FROM (TempClientTrialBalance INNER JOIN ClientAcctNbrs ON TempClientTrialBalance.ClientAcctNbr = ClientAcctNbrs.ClientAcctNbr) INNER JOIN (AcctNbrDescriptions INNER JOIN Acct_Nbr_Description_Effective ON AcctNbrDescriptions.AcctNbrDescrID = Acct_Nbr_Description_Effective.AcctNbr_Descr_ID) ON ClientAcctNbrs.ClientAcctNbr = Acct_Nbr_Description_Effective.ClientAcctNbr
ORDER BY TempClientTrialBalance.[Cost Period Ending], TempClientTrialBalance.ClientAcctNbr;