Access - Query for results where JOINS NOT equal.

Posted on 2012-08-15
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 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
    LVL 92

    Accepted Solution

    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.*
    FROM Table1 t1 INNER JOIN
        Table2 t2 ON t1.ID <> t2.ID

    Open in new window


    Author Closing Comment

    Thank you!! Thought there had to be a way.

    Author Comment

    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

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

    732 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

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now