Solved Accounting Income Statement Query (1 Viewer)

mhakim

Member
Local time
Today, 06:26
Joined
Jan 25, 2021
Messages
38
Dears
Good Day

are there any recomendation to get a query & Report like this

41100 Sales 5000
51100 Cost of goods sold ( 2000 )
_________________________________________
Gross profit 3000 Calculated
admin General Expenses (500)
other expenses (100)
_______________________________________
Income or loss by Operation (1500) Calculated
______________________________________________________________________________________________________
1- Table Data Transactions like this
Acc accname details debit Credit
____________________________________________________________________
41101 Sales sales invoice 0 1500
41102 sales sales invoice 0 3500
51101 Cost sales invoice 600 0
51102 Cost sales invoice 1400 0
61101 AdminExp salaries 500 0
71101 other other expenses 100 0
____________________________________________________________________
2- Table Chart of Account like this
Acc accname
______________________
41100 sales Total Title Account no Transactions
41101 Sales Transactions Account
41102 sales Transactions Account
51100 Cost Total Title Account no Transactions
51101 Cost Transactions Account
51102 Cost Transactions Account
61100 AdminExp Total Title Account no Transactions
61101 AdminExp Transactions Account
71100 other Total Title Account no Transactions
71101 other Transactions Account
____________________
 

June7

AWF VIP
Local time
Yesterday, 18:26
Joined
Mar 9, 2014
Messages
3,879
I don't see how anyone can advise without knowing your data structure. Most anything can be done with enough code.

My immediate recommendation would be to not reinvent the wheel. There are OTS accounting software that would be cheaper than cost of building from scratch.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 03:26
Joined
Feb 19, 2013
Messages
13,770
Consider using a union query to combine your accounts table with an aggregate query of the same table grouping on the first 3 digits of your nominal code
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 11:26
Joined
May 7, 2009
Messages
15,030
i made some "small" queries, for you to understand.
the FinalQuery is what you need.
 

Attachments

  • accounts.accdb
    440 KB · Views: 81

mhakim

Member
Local time
Today, 06:26
Joined
Jan 25, 2021
Messages
38
I don't see how anyone can advise without knowing your data structure. Most anything can be done with enough code.

My immediate recommendation would be to not reinvent the wheel. There are OTS accounting software that would be cheaper than cost of building from scratch.
data struture is clear at end of my question

if you donot have an answer
please take a seat and wait for the answer
 

Gasman

Enthusiastic Amateur
Local time
Today, 03:26
Joined
Sep 21, 2011
Messages
9,582
data struture is clear at end of my question

if you donot have an answer
please take a seat and wait for the answer
Damn, my list just gets bigger with comments like these. :(
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:26
Joined
Feb 19, 2002
Messages
34,426
nice to meet you
Welcome aboard. A suggestion - stop the attitude. People here are not getting paid to help you. If you treat people with respect, you will find that respect returned. Or, you can have people put you on their ignore list. Your choice. So far you've antagonized at least three people. Quite a debut.

Reports will do sorting and multi-level grouping without you having to create multiple queries. Using the report features allows you to have multiple summary levels. Do you know how to create a report that groups and summarizes? If you don't, we can get you started with that since what you want is a report anyway. In order to do the grouping, you need to define how to parse the account numbers. If you want nice headings for the parts, you will need additional tables that define them as well as your grouping levels. Once the table schema is correct, the report is straightforward and doesn't require multiple queries and unions.
 

mhakim

Member
Local time
Today, 06:26
Joined
Jan 25, 2021
Messages
38
Welcome aboard. A suggestion - stop the attitude. People here are not getting paid to help you. If you treat people with respect, you will find that respect returned. Or, you can have people put you on their ignore list. Your choice. So far you've antagonized at least three people. Quite a debut.

Reports will do sorting and multi-level grouping without you having to create multiple queries. Using the report features allows you to have multiple summary levels. Do you know how to create a report that groups and summarizes? If you don't, we can get you started with that since what you want is a report anyway. In order to do the grouping, you need to define how to parse the account numbers. If you want nice headings for the parts, you will need additional tables that define them as well as your grouping levels. Once the table schema is correct, the report is straightforward and doesn't require multiple queries and unions.
i am sorry
i respect all

i just want to get into the point to learn from many perspectives
there is many ways to do the job but
reach the perfect and easy way is not that easy
so we have to be effective and effecient you put your way and explain how you do it to reach the best way to get to the point

people learn from real life professional Examples
accounting income statement is a professional Examples
so ,
let participants and you try to do the job and compare to get to the best and easy way to do it
 

CJ_London

Super Moderator
Staff member
Local time
Today, 03:26
Joined
Feb 19, 2013
Messages
13,770
you say people learn from real life examples and accounting is a professional requirement. This forum is about programming. There are accountants here (I’m one) but database principles remain the same regardless of the purpose of the app.

so you want the best and easy way?. There is no such thing as this depends you where you are coming from and where you are going

You have not provided enough information for us to provide that. I can say that as an accountant and developer I would not store the data as you have with separate columns for debit and credits or apparently a text code as a pk. For me that is not best and easy. For you perhaps easy, maybe not best. So I would be starting from a different place and require a different solution

I have not looked at arnel’s solution so don’t know if he went the query route or the report route - both are potentially valid but would potentially require different query solutions since reports have their own way of grouping
 

mike60smart

Registered User.
Local time
Today, 03:26
Joined
Aug 6, 2017
Messages
1,047
i am sorry
i respect all

i just want to get into the point to learn from many perspectives
there is many ways to do the job but
reach the perfect and easy way is not that easy
so we have to be effective and effecient you put your way and explain how you do it to reach the best way to get to the point

people learn from real life professional Examples
accounting income statement is a professional Examples
so ,
let participants and you try to do the job and compare to get to the best and easy way to do it
It would help if you can upload a zipped copy of the database.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:26
Joined
Feb 19, 2002
Messages
34,426
There is rarely only a single path to complete a task. Some will be simpler than others, some will execute faster, Some will require code or queries and some can be achieved with simple property settings. The latter is what I am suggesting. It is the "Access" way because the original designers included it in their plan so it as optimized as they can make it. It is very unlikely that you could come up with a more efficient solution. So, I always start with the "Access" way and if it provides what I need and is fast enough to not annoy the users. I stop. There is no reason to look for a "perfect" solution. Frequently, the Access way is also the simplest to implement because it doesn't require code or queries. Some people think that it is inferior because it doesn't tax their technical skills. Those people should not be using Access because they eschew the best solutions Access has to offer because they didn't code them.
 

mhakim

Member
Local time
Today, 06:26
Joined
Jan 25, 2021
Messages
38
i made some "small" queries, for you to understand.
the FinalQuery is what you need.
hi arnelgp

your work is great it gets to the point

but really i donot understand your code
would you explain your code and how you do
how your code affect the queries

Public Function mySplit(ByVal sText As String, ByVal pos As Integer, Optional ByVal delim As String = " ")
mySplit = ""
On Error Resume Next
mySplit = Split(sText, delim)(pos - 1)
End Function
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 11:26
Joined
May 7, 2009
Messages
15,030
you can use the function or you don't use it.
the function only returns the "first" word in accName.
example:

tblChartOfAccounts tblChartOfAccounts

IDAccAccNameDbCr
1​
41100sales Total Title Account no TransactionsCR
the first "word" on AccName is "sales".
AcctName: mySplit([AccName],1," ")

if you want to return the "second" word ("Total")
AcctName: mySplit([AccName],2," ")
 

CJ_London

Super Moderator
Staff member
Local time
Today, 03:26
Joined
Feb 19, 2013
Messages
13,770
the usual and more flexible way to build a set of accounts is to have a mapping table to map nominal codes to where you want them to appear. Reasons are

a) treatment of balance sheet is different to P&L, except perhaps in the first year,

b) nominal codes get set up in the wrong place (or a change in policy makes it so)

c) sometimes nominal codes need to be combined differently (e.g. codes for employee expenses are split between functions - sales/marketing/production/admin and in one view need to be shown as part of each of those functions and in another combined as a single 'employee expenses' value.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:26
Joined
Feb 19, 2002
Messages
34,426
the usual and more flexible way to build a set of accounts is to
Which is exactly what I suggested back in post #9. CJ has added more details.
 

mhakim

Member
Local time
Today, 06:26
Joined
Jan 25, 2021
Messages
38
hi dears

i would like to thank all of you for your help
it is more than appreciated

finally i get to a point which i think it is very good
attached my final incomestatement report view

but i need extra help as attached in image
 

Attachments

  • income-statement report.pdf
    779.8 KB · Views: 79

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 11:26
Joined
May 7, 2009
Messages
15,030
you can use a Query to total those Main accounts.
or you can use DSum() in your report.
see report1.
 

Attachments

  • accounts.accdb
    1 MB · Views: 67

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:26
Joined
Feb 19, 2002
Messages
34,426
There is no need to use extra queries or domain functions. You need to add grouping to the report. I can't tell by what I see on the report what you need to group by but I talked about that in an earlier post in this thread. Something in your data needs to identify 411 and 511 as belonging together and 521, 531, 431, and 551 as belonging together. Once you identify that, THAT is what you group on. Then you have to decide what column (D or C) you want the calculation to appear in. Do you want it to be in D if it is a debit and C if it is a credit or always in C.

The ControlSource of the calculated field will look something like:

=(Sum(Credit) - Sum(Debit)) --- if the result will always be in the same column regardless of whether the result is a debit or credit. If you want the result to swap, then the calculation is more complicated. Just let us know.

The "grand" total for this field will have the identical calculation. Access keeps track of what level of summation you are working with.
 

Users who are viewing this thread

Top Bottom