Access - Query for results where JOINS NOT equal.
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_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:
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;
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
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].[ClientAcct
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.
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].[ClientAcct
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial