Grouping query

[Mphiri]

I have a table called TB which has PrevYear(Data type Currency), Debit(Data type Currency), Credit(Data type Currency), Description(Data type Text), Category(Data type Text). Category can be any of the following; revenue, expense, asset, liability etc.
I want to create a report/query which groups the categories as follows;

Revenue 2009(Debit/Credit) 2008(PrevYear)
Credit Sales
Cash Sales
Subtotal
Expenses
Wages
Travel
Subtotal

Profit/Loss

Can anyone help with this?
Michael

[llkhoutx]

financial rollups in Access are not very complicated, but extremely tedious. Stack the various rows and columns in a temporary table and then report that table, with appropriate grouping. You'll end up with multiple append queries and a finally summing query. It's not work for a neophyte.

[Mphiri]

Thanks llkhoutx, I will appreciate your response.

[evander]

Hi Michael. I'm an accountant. Maybe I can help. I understand you're trying to make a statement of comprehensive income out of your table. Just curious, did you structure your balance sheet and income statement to conform to the exact grouping you are after?

[Mphiri]

Hi Michael. I'm an accountant. Maybe I can help. I understand you're trying to make a statement of comprehensive income out of your table. Just curious, did you structure your balance sheet and income statement to conform to the exact grouping you are after?

Thanks Evander, I have structured my tbTrialBalance in such a way that I have among other fields, class and category. Class can be any of assets, revenue, expense, capital, liability etc(basically balance sheet structure), then category has interest earned, sales revenue, cost of sales, salaries and wages etc. I have decided to group by category for the notes to the financial statements, then for the statement of financial position and comprehensive income, I am grouping by class. Am I on the right track? Is this ok and is there anything else I need to do?

[evander]

I assume you want both Class and Category to be both displayed in your trial balance. Or, do you want a separate presentation for class, and another one for category (hence, two different Group By queries)?

If your goal is to present your trial balance both by class and category, you will need to structure your Chart of Accounts master list to contain the following fields:

-AccountID
-AccountName
-Statement
-Class
-Category

Try looking at a sample chart of accounts:
http://rapidshare.com/files/39819940...lance.xls.html

Then, create a query that groups your debits and credits in the following order (left to right, if you are using the query-by-example window):
-Statement (Group by)
-Class (Group by)
-Category (Group by)
-AccountID (Group by)
-AccountName (Group by)
-Debit (Sum)
-Credit (Sum)

If you wanted to produce a trial balance with accounting periods presented the way you showed in this post, I would rather give you a tutorial via TeamViewer instead, 'cause enumerating the steps will make this post quite long.

Tell me when you have implemented the Chart of Accounts, and I will show you what to do next.

Good luck.

[Mphiri]

I assume you want both Class and Category to be both displayed in your trial balance. Or, do you want a separate presentation for class, and another one for category (hence, two different Group By queries)?

If your goal is to present your trial balance both by class and category, you will need to structure your Chart of Accounts master list to contain the following fields:

-AccountID
-AccountName
-Statement
-Class
-Category

Try looking at a sample chart of accounts:
http://rapidshare.com/files/39819940...lance.xls.html

Then, create a query that groups your debits and credits in the following order (left to right, if you are using the query-by-example window):
-Statement (Group by)
-Class (Group by)
-Category (Group by)
-AccountID (Group by)
-AccountName (Group by)
-Debit (Sum)
-Credit (Sum)

If you wanted to produce a trial balance with accounting periods presented the way you showed in this post, I would rather give you a tutorial via TeamViewer instead, 'cause enumerating the steps will make this post quite long.

Tell me when you have implemented the Chart of Accounts, and I will show you what to do next.

Good luck.

Evander, you are amazing.
I have attached the table called TrialBalance as I would want it to be and the query aclled GroupedTrialBalance as you have instructed, both as text files.
I hope I have followed your instructions.
Thanks

[evander]

Thanks for the compliment, Mphiri.
Did you get the results you wanted?

To create your grouped income statement, create a query that groups your AccountID's or AccountName's:

(1) Statement (Group by)
(2) Class (Group by)
(3) Category (Group by)
(4) Amount: Credit - Debit (Sum)


This query casts credit-balance accounts in positive amounts, and debit-balance amounts in negative.

================================================== ====

I'm inspecting your raw trial balance and the grouped one. I want you to give the following recommendations, if these are feasible on your part:

(1) Account names (or account titles) need to be presented as they would appear in an external (or audited) financial statements, such as sales, cost of sales... If you want to create "departments" or subdivisions in the SALES account (such as Sales - Insecticides, Sales - Fertilizers), you can try to create a separate column identifier instead.

Thus, when you're recording a journal entry to record Sales on account:

AccountID      AccountName               SubledgerID         Debit(Credit)
1101             Accounts Receivable      Mr. Smith              100,000.00
5001             Sales                           Insecticides           (50,000.00)
5001             Sales                           Fertilizers              (50,000.00)

(2) If your general ledger system uses a pure debit-credit recording method, you should use a single-column amount instead, with debits expressed as positive amounts, and credits expressed as negative. Creating separate columns for debits and credits results to unused cells.


Good luck dude.

[Mphiri]

Thanks for the compliment, Mphiri.
Did you get the results you wanted?

To create your grouped income statement, create a query that groups your AccountID's or AccountName's:

(1) Statement (Group by)
(2) Class (Group by)
(3) Category (Group by)
(4) Amount: Credit - Debit (Sum)


This query casts credit-balance accounts in positive amounts, and debit-balance amounts in negative.

================================================== ====

I'm inspecting your raw trial balance and the grouped one. I want you to give the following recommendations, if these are feasible on your part:

(1) Account names (or account titles) need to be presented as they would appear in an external (or audited) financial statements, such as sales, cost of sales... If you want to create "departments" or subdivisions in the SALES account (such as Sales - Insecticides, Sales - Fertilizers), you can try to create a separate column identifier instead.

Thus, when you're recording a journal entry to record Sales on account:

AccountID AccountName SubledgerID Debit(Credit)
1101 Accounts Receivable Mr. Smith 100,000.00
5001 Sales Insecticides (50,000.00)
5001 Sales Fertilizers (50,000.00)

(2) If your general ledger system uses a pure debit-credit recording method, you should use a single-column amount instead, with debits expressed as positive amounts, and credits expressed as negative. Creating separate columns for debits and credits results to unused cells.


Good luck dude.

Thanks again man, I seem to able to tell my right hand from my left one now in this regard, thanks to you. I am surely getting somewhere and if I get stuck again I hope I am welcome to call on you.
Good day and God bless you mightily.

[evander]

No problem dude. Glad I can help. Keep on practicing.

[Mphiri]

No problem dude. Glad I can help. Keep on practicing.

I will pal, thanks.