We help IT Professionals succeed at work.
Get Started

Access - Query for results where JOINS NOT equal.

wlwebb
wlwebb asked
on
687 Views
Last Modified: 2012-08-16
Hello All...

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_Effective.

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;

Open in new window

Comment
Watch Question
CERTIFIED EXPERT
Top Expert 2010
Commented:
This problem has been solved!
Unlock 1 Answer and 3 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE