asked on

Access - Query Trial Balance and Adjusting Entries

I have a db of multiple period end trial balances. I have those trial balances in a table called ClientTrialBalance. That table has 4 fields
TrialBalID (Primary Key)
PeriodEnding (Date)
ClientAcctNbr (Text xxx-xx-xxx)
EndingBalance (Currency)

I have a second and third tables to record adjusting entries.

The second table is AJE_Description where I have the AJE description and the Period Ending Date that it relates to. The fields I included in that table is:

AJE_Descr_ID (Primary Key)
AJE_Description (Text)
PeriodEndingDate

The third Table is the details of the AJE.
The fields are
AJEID (Primary Key)
ClientAcctNbr(Text xxx-xx-xxx)
AJEAmount
AJE_Descr_ID(Foreign Key)

When I pull an adjusted trial balance I can either get the account# and all of the aje's including amounts not related to that period. OR Adjusted balance of just accounts that were adjusted.

SELECT ClientTrialBalance.ClientTrialBalID, ClientTrialBalance.ClientAcctNbr_DescrID, ClientTrialBalance.[Cost Period Ending], ClientTrialBalance.TrialBalStatus, Acct_Nbr_Description_Effective.AcctNbr_Descr_ID, Acct_Nbr_Description_Effective.ClientAcctNbr, AcctNbrDescriptions.Description, ClientTrialBalance.UnadjClientBal, qry_AdjustingEntries.AJEAmt
FROM (qry_AdjustingEntries RIGHT JOIN ClientAcctNbrs ON qry_AdjustingEntries.AdjustingEntries.ClientAcctNbr = ClientAcctNbrs.ClientAcctNbr) RIGHT JOIN (AcctNbrDescriptions RIGHT JOIN (Acct_Nbr_Description_Effective RIGHT JOIN ClientTrialBalance ON Acct_Nbr_Description_Effective.AcctNbr_Descr_ID = ClientTrialBalance.ClientAcctNbr_DescrID) ON AcctNbrDescriptions.AcctNbrDescrID = Acct_Nbr_Description_Effective.AcctNbr_Descr_ID) ON ClientAcctNbrs.ClientAcctNbr = Acct_Nbr_Description_Effective.ClientAcctNbr
WHERE (((qry_AdjustingEntries.AJECostPeriodDate)=[ClientTrialBalance].[Cost Period Ending]))
GROUP BY ClientTrialBalance.ClientTrialBalID, ClientTrialBalance.ClientAcctNbr_DescrID, ClientTrialBalance.[Cost Period Ending], ClientTrialBalance.TrialBalStatus, Acct_Nbr_Description_Effective.AcctNbr_Descr_ID, Acct_Nbr_Description_Effective.ClientAcctNbr, AcctNbrDescriptions.Description, ClientTrialBalance.UnadjClientBal, qry_AdjustingEntries.AJEAmt
ORDER BY ClientTrialBalance.[Cost Period Ending], Acct_Nbr_Description_Effective.ClientAcctNbr;

Open in new window

In this code I put a Where stmt attempting to match AJE's to accounts that were adjusted for that particular period but need all accounts even if they weren't adjusted.

Any help on how to change this?

Lee Wadwell

When using OUTER joins ... when the join fails, the columns from that table are returned as NULL. Hence in the WHERE clause, outer joined columns may be null - and any clause that uses these columns needs to cater for NULL or, if it is part of the join, put in the ON clause.

Lee Wadwell

This (hopefully) is your query using LEFT JOIN instead of RIGHT JOIN with the WHERE predicate moved into the LEFT JOIN ... ON clause.

SELECT ClientTrialBalance.ClientTrialBalID
     , ClientTrialBalance.ClientAcctNbr_DescrID
     , ClientTrialBalance.[Cost Period Ending]
     , ClientTrialBalance.TrialBalStatus
     , Acct_Nbr_Description_Effective.AcctNbr_Descr_ID
     , Acct_Nbr_Description_Effective.ClientAcctNbr
     , AcctNbrDescriptions.Description
     , ClientTrialBalance.UnadjClientBal
     , qry_AdjustingEntries.AJEAmt
FROM ((((          ClientTrialBalance
         LEFT JOIN Acct_Nbr_Description_Effective
         ON Acct_Nbr_Description_Effective.AcctNbr_Descr_ID = ClientTrialBalance.ClientAcctNbr_DescrID)
        LEFT JOIN AcctNbrDescriptions
        ON AcctNbrDescriptions.AcctNbrDescrID = Acct_Nbr_Description_Effective.AcctNbr_Descr_ID)
       LEFT JOIN ClientAcctNbrs
       ON ClientAcctNbrs.ClientAcctNbr = Acct_Nbr_Description_Effective.ClientAcctNbr)
      LEFT JOIN qry_AdjustingEntries
      ON qry_AdjustingEntries.ClientAcctNbr = ClientAcctNbrs.ClientAcctNbr
         AND qry_AdjustingEntries.AJECostPeriodDate=[ClientTrialBalance].[Cost Period Ending])
GROUP BY ClientTrialBalance.ClientTrialBalID
       , ClientTrialBalance.ClientAcctNbr_DescrID
       , ClientTrialBalance.[Cost Period Ending]
       , ClientTrialBalance.TrialBalStatus
       , Acct_Nbr_Description_Effective.AcctNbr_Descr_ID
       , Acct_Nbr_Description_Effective.ClientAcctNbr
       , AcctNbrDescriptions.Description
       , ClientTrialBalance.UnadjClientBal
       , qry_AdjustingEntries.AJEAmt
ORDER BY ClientTrialBalance.[Cost Period Ending]
       , Acct_Nbr_Description_Effective.ClientAcctNbr;

Open in new window

Lee Wadwell

Or to allow for NULLS with the outer join in the WHERE clause

SELECT ClientTrialBalance.ClientTrialBalID, ClientTrialBalance.ClientAcctNbr_DescrID, ClientTrialBalance.[Cost Period Ending], ClientTrialBalance.TrialBalStatus, Acct_Nbr_Description_Effective.AcctNbr_Descr_ID, Acct_Nbr_Description_Effective.ClientAcctNbr, AcctNbrDescriptions.Description, ClientTrialBalance.UnadjClientBal, qry_AdjustingEntries.AJEAmt
FROM (qry_AdjustingEntries RIGHT JOIN ClientAcctNbrs ON qry_AdjustingEntries.AdjustingEntries.ClientAcctNbr = ClientAcctNbrs.ClientAcctNbr) RIGHT JOIN (AcctNbrDescriptions RIGHT JOIN (Acct_Nbr_Description_Effective RIGHT JOIN ClientTrialBalance ON Acct_Nbr_Description_Effective.AcctNbr_Descr_ID = ClientTrialBalance.ClientAcctNbr_DescrID) ON AcctNbrDescriptions.AcctNbrDescrID = Acct_Nbr_Description_Effective.AcctNbr_Descr_ID) ON ClientAcctNbrs.ClientAcctNbr = Acct_Nbr_Description_Effective.ClientAcctNbr
WHERE (((qry_AdjustingEntries.AJECostPeriodDate)=[ClientTrialBalance].[Cost Period Ending] OR qry_AdjustingEntries.AJECostPeriodDate IS NULL))
GROUP BY ClientTrialBalance.ClientTrialBalID, ClientTrialBalance.ClientAcctNbr_DescrID, ClientTrialBalance.[Cost Period Ending], ClientTrialBalance.TrialBalStatus, Acct_Nbr_Description_Effective.AcctNbr_Descr_ID, Acct_Nbr_Description_Effective.ClientAcctNbr, AcctNbrDescriptions.Description, ClientTrialBalance.UnadjClientBal, qry_AdjustingEntries.AJEAmt
ORDER BY ClientTrialBalance.[Cost Period Ending], Acct_Nbr_Description_Effective.ClientAcctNbr;

Open in new window

wlwebb

ASKER

lwadwell,
The last posting (with the Nulls and Right Joins) does appear to work) Can you enlighten a novice regarding the evils of the Right Join.... I have seen a couple previous posts warning against it and want to understand the Why.

AS A FOOTNOTE:
The 2nd posting where I think you were helping to get it rewritten to Left Joins fails and gives the message "JOIN expression not supported".

I just finished going thru the result of the last posting. Something is not correct because the trial balances don't balance ..... But the actual original table where the query comes from does in fact balance

Lee Wadwell

Did the LEFT join version work?

The 'evil' is the OUTER JOIN in the first place ... whether LEFT or RIGHT. But if I must outer join - and there is no avoiding it at time, I do prefer LEFT joins.

The order in which the joins are done - particularly in ACCESS which makes you put brackets "()" around everything can effect things greatly. It comes down to how a visualise the data and work methodically out from there. So that I start with the 'mandatory' table (or driving table as I call it) and then join out from there. i.e.
FROM mandatory_table LEFT JOIN optional_table
A RIGHT join would be:
FROM optional_table RIGHT JOIN mandatory_table

If you are only doing one ... it is a much of a muchness - personal preferences only. When you start to nest the outer joins and do more it gets odd. You really have to think about the order and the nesting in brackets.

Attached is a simple access database with some examples to hopefully illustrate my point.
Q-27827609.accdb

wlwebb

ASKER

No, the left Join keeps giving me an error JOIN expression not supported.... ????? don't know why.

JUST AS AN FYI regarding this Trial Balance...
Usually I would put the Account Descriptions in the table with the account numbers. That would make too much sense huh....

However, Problem with this application is this...I have to be able years later to reproduce the Trial Balances with the Account Name that was used years prior. IE: I might have to be able to print a trial balance from 5 years ago that shows the old account title even though that account number's title was changed 5 years later... (it's a government thing).

Thus the only way I could figure out how to do that was to make 3 tables, 1 being the account numbers, 1 being the account descriptions (with effective date) and 1 being a combining of the account number and name.

wlwebb

ASKER

By the way .... your db won't open.......All I get is a new window with garbage on the screen

Lee Wadwell

What version of Access are you using? I am attaching again for older Access versions.

It contains tables and sample queries/views.
Q-27827609.mdb

wlwebb

ASKER

A2007

wlwebb

ASKER

Here is a stripped down version of my total DB... Don't know if you can tell from that where I have gone wrong given the limitation on the Account Descriptions issue.
Sample-1.accdb

Lee Wadwell

I am wondering what is the problem with the LEFT join version... hard for me to debug without an example database. I do remember thinking that
"qry_AdjustingEntries.AdjustingEntries.ClientAcctNbr" was odd with too many period marks and may have changed it. Putting it back would make it:

SELECT ClientTrialBalance.ClientTrialBalID
     , ClientTrialBalance.ClientAcctNbr_DescrID
     , ClientTrialBalance.[Cost Period Ending]
     , ClientTrialBalance.TrialBalStatus
     , Acct_Nbr_Description_Effective.AcctNbr_Descr_ID
     , Acct_Nbr_Description_Effective.ClientAcctNbr
     , AcctNbrDescriptions.Description
     , ClientTrialBalance.UnadjClientBal
     , qry_AdjustingEntries.AJEAmt
FROM ((((          ClientTrialBalance
         LEFT JOIN Acct_Nbr_Description_Effective
         ON Acct_Nbr_Description_Effective.AcctNbr_Descr_ID = ClientTrialBalance.ClientAcctNbr_DescrID)
        LEFT JOIN AcctNbrDescriptions
        ON AcctNbrDescriptions.AcctNbrDescrID = Acct_Nbr_Description_Effective.AcctNbr_Descr_ID)
       LEFT JOIN ClientAcctNbrs
       ON ClientAcctNbrs.ClientAcctNbr = Acct_Nbr_Description_Effective.ClientAcctNbr)
      LEFT JOIN qry_AdjustingEntries
      ON qry_AdjustingEntries.AdjustingEntries.ClientAcctNbr = ClientAcctNbrs.ClientAcctNbr
         AND qry_AdjustingEntries.AJECostPeriodDate=[ClientTrialBalance].[Cost Period Ending])
GROUP BY ClientTrialBalance.ClientTrialBalID
       , ClientTrialBalance.ClientAcctNbr_DescrID
       , ClientTrialBalance.[Cost Period Ending]
       , ClientTrialBalance.TrialBalStatus
       , Acct_Nbr_Description_Effective.AcctNbr_Descr_ID
       , Acct_Nbr_Description_Effective.ClientAcctNbr
       , AcctNbrDescriptions.Description
       , ClientTrialBalance.UnadjClientBal
       , qry_AdjustingEntries.AJEAmt
ORDER BY ClientTrialBalance.[Cost Period Ending]
       , Acct_Nbr_Description_Effective.ClientAcctNbr;

Open in new window

wlwebb

ASKER

I think our posts crossed in the "mail"...

wlwebb

ASKER

The SQL 2 posts above this still gives me the JOIN expression not supported error

Lee Wadwell

yup ... having a look now.

Lee Wadwell

arrgghhh ... I keep falling into that trap - could not specify columns from two different tables in an outer join ON clause. Has to be done as a WHERE (or in-line views - which would be overkill).

SELECT ClientTrialBalance.ClientTrialBalID
     , ClientTrialBalance.ClientAcctNbr_DescrID
     , ClientTrialBalance.[Cost Period Ending]
     , ClientTrialBalance.TrialBalStatus
     , Acct_Nbr_Description_Effective.AcctNbr_Descr_ID
     , Acct_Nbr_Description_Effective.ClientAcctNbr
     , AcctNbrDescriptions.Description
     , ClientTrialBalance.UnadjClientBal
     , qry_AdjustingEntries.AJEAmt
FROM ((((          ClientTrialBalance
         LEFT JOIN Acct_Nbr_Description_Effective
         ON Acct_Nbr_Description_Effective.AcctNbr_Descr_ID = ClientTrialBalance.ClientAcctNbr_DescrID)
        LEFT JOIN AcctNbrDescriptions
        ON AcctNbrDescriptions.AcctNbrDescrID = Acct_Nbr_Description_Effective.AcctNbr_Descr_ID)
       LEFT JOIN ClientAcctNbrs
       ON ClientAcctNbrs.ClientAcctNbr = Acct_Nbr_Description_Effective.ClientAcctNbr)
      LEFT JOIN qry_AdjustingEntries
      ON qry_AdjustingEntries.AdjustingEntries.ClientAcctNbr = ClientAcctNbrs.ClientAcctNbr)
WHERE qry_AdjustingEntries.AJECostPeriodDate=[ClientTrialBalance].[Cost Period Ending]
OR qry_AdjustingEntries.AJECostPeriodDate IS NULL
GROUP BY ClientTrialBalance.ClientTrialBalID
       , ClientTrialBalance.ClientAcctNbr_DescrID
       , ClientTrialBalance.[Cost Period Ending]
       , ClientTrialBalance.TrialBalStatus
       , Acct_Nbr_Description_Effective.AcctNbr_Descr_ID
       , Acct_Nbr_Description_Effective.ClientAcctNbr
       , AcctNbrDescriptions.Description
       , ClientTrialBalance.UnadjClientBal
       , qry_AdjustingEntries.AJEAmt
ORDER BY ClientTrialBalance.[Cost Period Ending]
       , Acct_Nbr_Description_Effective.ClientAcctNbr;

Open in new window

wlwebb

ASKER

Thank you for the efforts.... However when I view the qry the trial balances are still "out of balance" trying to find out what it is leaving out or duplicating.......

wlwebb

ASKER

OK... what it is omitting from the trial balances is any account in another period that was adjusted in any other period. ?????????????????

IE: the only adjusting entry i have in the table right now effects two accounts for 1 cent each and it is in the first period trial balance that I have in the ClientTrialBalance table... account 0101101- and 0104101- those are the two accounts that it omits in subsequent trial balances...

ASKER CERTIFIED SOLUTION

Lee Wadwell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wlwebb

ASKER

Well that works...but you just lost me........ How does it get the table/query "V"???????????

wlwebb

ASKER

Given the limitation of the Descriptions being in another table.... Is there a "better" way that I could have defined these?????? Just trying to understand and learn.....

Lee Wadwell

To be able to do the LEFT JOIN on columns from two different table ... I needed to make it a an in-line view - same as a saved query/view ... just temporary. That in-line view I gave the alias of 'v'. By in-line view ... I mean a SELECT in a SELECT's FROM e.g.
SELECT ...
FROM (SELECT ... FROM ....) as v

Is there a better way? Maybe ... from what I can see and understand so far maybe, maybe not. As you need to keep 'historical' data ... it needs to be deref'd into separate table and this creates a 1:M relationship. Your approach seems solid to me ... could I make it 'better' - only maybe at best AND I would need to understand the data much better first.

wlwebb

ASKER

Thank you for your time! Still wondering though if a better way?

wlwebb

ASKER

Crossed in the "Mail" again.... I read the code and was able to understand what you were doing. First blush blew me away.....