Access - Query for results where JOINS NOT equal.

Posted on 2012-08-15
Medium Priority
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

Question by:wlwebb
  • 2
LVL 93

Accepted Solution

Patrick Matthews earned 2000 total points
ID: 38297780
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?

Yes, you can, but the trick is that you can only do it in the SQL View: the GUI Design View is only able to handle equi-joins.

So, for example, Access will allow this, but only if you do it in the SQL View:

SELECT t1.*, t2.*
    Table2 t2 ON t1.ID <> t2.ID

Open in new window


Author Closing Comment

ID: 38297916
Thank you!! Thought there had to be a way.

Author Comment

ID: 38302080
I have a technical problem when I did this.

If I just do as you indicated above I get a list where it shows my TempClientAcctNbr and then every ClientAcctNbr from the other table where they don't match...

What I am needing is it to give me a list of TempClientAcctNbr if that same number doesn't show up in the ClientAcctNbr table.  

SO in my inept ability I tried adding. a WHERE clause if the ClientAcctNbr table for that TempAcctNbr is null.

SELECT t1.*, t2.*
FROM TempClientTrialBalance AS t1, ClientAcctNbrs AS t2
WHERE (((IsNull([t2].[ClientAcctNbr]))=Tue));

I then went to my Temp table and changed one of the account number s so I knew I had a number that wasn't in the ClientAcctNbr table.

I don't get anything.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question