wlwebb
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.
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?
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;
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?
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.
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;
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;
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
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
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
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
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.
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.
ASKER
By the way .... your db won't open.......All I get is a new window with garbage on the screen
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
It contains tables and sample queries/views.
Q-27827609.mdb
ASKER
A2007
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
Sample-1.accdb
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.Adju stingEntri es.ClientA cctNbr" was odd with too many period marks and may have changed it. Putting it back would make it:
"qry_AdjustingEntries.Adju
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;
ASKER
I think our posts crossed in the "mail"...
ASKER
The SQL 2 posts above this still gives me the JOIN expression not supported error
yup ... having a look now.
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;
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.......
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...
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Well that works...but you just lost me........ How does it get the table/query "V"???????????
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.....
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.
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.
ASKER
Thank you for your time! Still wondering though if a better way?
ASKER
Crossed in the "Mail" again.... I read the code and was able to understand what you were doing. First blush blew me away.....