Query to get accounting balances

[jobbie]

I am running this query:

SELECT GL.Account_Name, Sum(Journal.Amount) AS Debit_Amount FROM GL INNER JOIN Journal ON GL.[Account_Name] = Journal.Debit_Ac
GROUP BY GL.Account_Name

which gives me the debit balance for the accounts in GL

Table "Journal" has 3 columns [Debit_Ac],[Credit_Ac] and [Amount]

Table "GL" has 1 column [Account_Name]

Now I would like the query to also display the credit balance as separate column and also another column to show the difference between the credit and debit.

I am using MS access 2007

[CJ_London]

doesn't look like your data is normalised, so you would need to use a union query to union the above query to a similar one linked to Journal.Credit_Ac

Suggest you review your table design so this sort of thing becomes much simpler

[jobbie]

doesn't look like your data is normalised, so you would need to use a union query to union the above query to a similar one linked to Journal.Credit_Ac

Suggest you review your table design so this sort of thing becomes much simpler

not sure how I could make my table design simpler than this. Can you please give me some tips?

Also wouldn't using a union query place all the records in the existing columns instead of creating a new one for credit balances?

[CJ_London]

without seeing your full table design, difficult to say but I would design this

Table "Journal" has 3 columns [Debit_Ac],[Credit_Ac] and [Amount]

as

Table "Journal" has 3 columns [Account_Name],[Account_Type] and [Amount]

Where Account_Type would be Debit or Credit

then you would use a crosstab query with accountname as rowheader, accounttype as column header and amount as value

Also wouldn't using a union query place all the records in the existing columns

you would need to create two columns

SELECT GL.Account_Name, Sum(Journal.Amount) AS Debit_Amount, 0 AS Credit_Amount FROM GL INNER JOIN Journal ON GL.[Account_Name] = Journal.Debit_Ac
GROUP BY GL.Account_Name
UNION SELECT GL.Account_Name, 0 AS Debit_Amount, Sum(Journal.Amount) AS Credit_Amount FROM GL INNER JOIN Journal ON GL.[Account_Name] = Journal.Credit_Ac
GROUP BY GL.Account_Name

[jobbie]

without seeing your full table design, difficult to say but I would design this

Table "Journal" has 3 columns [Debit_Ac],[Credit_Ac] and [Amount]

as

Table "Journal" has 3 columns [Account_Name],[Account_Type] and [Amount]

Where Account_Type would be Debit or Credit

then you would use a crosstab query with accountname as rowheader, accounttype as column header and amount as value

you would need to create two columns

SELECT GL.Account_Name, Sum(Journal.Amount) AS Debit_Amount, 0 AS Credit_Amount FROM GL INNER JOIN Journal ON GL.[Account_Name] = Journal.Debit_Ac
GROUP BY GL.Account_Name
UNION SELECT GL.Account_Name, 0 AS Debit_Amount, Sum(Journal.Amount) AS Credit_Amount FROM GL INNER JOIN Journal ON GL.[Account_Name] = Journal.Credit_Ac
GROUP BY GL.Account_Name

Don't mean to offend you but do you know accounting? Because it seems this may not work as in accounting when there is a debit entry there is also a corresponding credit entry to another account.

[CJ_London]

err - yes - I'm a FCMA/CGMA and been CFO/CFC of multinational companies. I now work as a consultant to companies large and small designing and often building cross system financial control systems typically required as a result of acquisitions, change of systems or regulatory change. I did say without seeing your full table design, difficult to say.

[jobbie]

err - yes - I'm a FCMA/CGMA and been CFO/CFC of multinational companies. I now work as a consultant to companies large and small designing and often building cross system financial control systems typically required as a result of acquisitions, change of systems or regulatory change. I did say without seeing your full table design, difficult to say.

ok my apologies but my intention was not to offend you.

Actually I was able to solve the problem by using 2 separate queries as per your suggestion. If anything else comes up I will post it here. I won't mark the post as solved until then. Thanks

[CJ_London]

no offence taken

[jobbie]

ok so now I am facing an obstacle. I have the following queries:

Debit_Balances:

SELECT GL.Account_Name, sum(nz([Journal].[Amount],0)) AS Debit_AmountFROM GL LEFT JOIN Journal ON GL.Account_Name = Journal.Debit_Ac
GROUP BY GL.Account_Name
HAVING ((GL.Account_Name)=[Forms]![Balance_sheet]![Text0]);

Credit_Balances:

SELECT GL.Account_Name, nz(Sum([Journal].[Amount])) AS Credit_Amount
FROM GL LEFT JOIN Journal ON GL.Account_Name = Journal.Credit_Ac
GROUP BY GL.Account_Name
HAVING (((GL.Account_Name)=[Forms]![Balance_sheet]![Text0]));

and

Final_Query:

SELECT Credit_Balances.Credit_Amount, Debit_Balances.Debit_Amount
FROM Credit_Balances INNER JOIN Debit_Balances ON Credit_Balances.Account_Name = Debit_Balances.Account_Name;

but Final_Query returns blank columns

I would like to find the difference between the Debit and Credit amounts in the above 2 queries in a 3rd query

[CJ_London]

do your first two queries both produce values?