Balance for company for selected month (1 Viewer)

hatmak

Registered User.
Local time
Today, 09:53
Joined
Jan 17, 2015
Messages
121
hi all

how make month balance for any selected month

beginning month balance ----- end month balance

i have data_tb which continue from data for many company with her monthly movements and credit or debit

credit it's meaning ( money deserved to our company )
debit --- payments or any discount to other company

my data more than 9000 record and for 9 months

i want to make query when select any company

like
EXCEL SHEET ATTACHED

company B---- 01/2021

DEBIT​
CREDIT​
beginning BLANCE 01/2021​
0​
0​
ENDING MONTH BLANCE_01/2021​
2650​
0​
TOTAL_INTEREST_01/2021​
0​
150​
TOTAL_Monthly Subscription 01/2021​
0​
1200​
TOTAL PAYMENT01/2021​
4000​
0​

02/2021


DEBIT​
CREDIT​
beginning BLANCE 02/2021 ( IS ENDING MONTH BLANCE 01/2021)​
2650​
0​
ENDING MONTH BLANCE_02/2021​
250​
0​
TOTAL_INTEREST_02/2021​
0​
150​
TOTAL_Monthly Subscription02/2021​
0​
1500​
TAOTAL_Tax​
0​
100​
TOTAL PAYMENT_02/2021​
2000​
0​


Attached example for company b in 01/2021 and 02/2021

regards
 

Attachments

  • DATA.accdb
    756 KB · Views: 300
  • EXM.ZIP
    10.1 KB · Views: 231

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:53
Joined
Feb 19, 2002
Messages
42,971
You need two queries. One to calculate the beginning balance and one to calculate the ending balance and possibly include details if that's what you want to see.. Use a union query to get them all together. Add a dummy field so you can ensure that the beginning bal record sorts foist for each group. Busy now, more help later if you still need it.
 

hatmak

Registered User.
Local time
Today, 09:53
Joined
Jan 17, 2015
Messages
121
i want begging and ending balance for any month i select it
still need help

i can't do it
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:53
Joined
Feb 19, 2002
Messages
42,971
Add an unbound control on your form. The following is a sample union query

Select Company, Sum(TransAmt) As Bal, "A" As SortField
From Your Table
Where TransDT < Forms!YourForm!MonthStartDT
Group By Company, "A"
Union Select Company, Sum(TransAmt) As Bal, "B" As SortField
From Your Table
Where TransDT >= Forms!YourForm!MonthStartDT
Group By Company, "B"
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:53
Joined
Feb 19, 2002
Messages
42,971
I gave you the query. You need the form to collect the date variable and to run the query. If you don't want to use the form to collect the date argument, you can prompt for it but the form is a superior method
 

hatmak

Registered User.
Local time
Today, 09:53
Joined
Jan 17, 2015
Messages
121
You need two queries. One to calculate the beginning balance and one to calculate the ending balance and possibly include details if that's what you want to see.. Use a union query to get them all together. Add a dummy field so you can ensure that the beginning bal record sorts foist for each group. Busy now, more help later if you still need it.
how to collect balance for each company for separate month

like

when run query month 3 for company b

i need beginning balance for 03/2021 ( which are ending balance for month 02/2021)
and run total for all other movements form month 3/2021
and get ending balance which are diff between credit and debit for all movements for 03/2021
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:53
Joined
Feb 19, 2002
Messages
42,971
Then you need three queries. The third query in the union returns the details for the specified month. You also need to add dummy fields to the other two queries to allow for the additional fields you need in the third query.

I'm not sure why you are not doing this with a report, Queries are not used to present data. Usng a report also allows you to simplify the query since you don't need to aggregate the current month. The first query in the union would get the beginning balance. The second query in the union would get the details for the month in question. The report would do the rest of the aggregation.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:53
Joined
May 7, 2009
Messages
19,169
here is a demo.
 

Attachments

  • DATA.accdb
    648 KB · Views: 279

hatmak

Registered User.
Local time
Today, 09:53
Joined
Jan 17, 2015
Messages
121
many thanks for your help

arnelgp


is possible to view all data in query for for running month in form

and i need to do all queries in my database please explain query 8_qryIncomeStatement

i have many tables in my database

thanks
 

Cotswold

Active member
Local time
Today, 16:53
Joined
Dec 31, 2020
Messages
521
Hatmak, if you have many Tables you will have very, very many Queries. Maybe well over ten times more Queries
than tables in a complex system. These will vary from simple queries to compound Queries within Queries. You
will need to understand them from the start, plus you must always check that the results they provide are actually
correct. Once you understand them they are a valuable tool.
Failing to check the output is where the spreadsheet brigade fail, and why virtually every spreadsheet has an
error embedded somewhere. It never ceases to amaze me just how much reliance and trust is given to spreadsheets.

Check, check and check again your Queries. If they are complex, or compound, once they are 100% correct do not
alter them. Instead if changes are needed, then create new from the start and retain the earlier version so you can
easily switch back, or refer to them. Making changes to complex Queries can be a can of worms and maybe avoided?

Just one more thing. If you change a Table Name, or a Field Name in a table. Access will happily cascade those changes
through your Queries without drawing your attention to it. When that happens, the code in any associated Queries will
fall over. Clearly, adding new Fields to a Table will not affect your Queries.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:53
Joined
Feb 19, 2002
Messages
42,971
Access will happily cascade those changes
through your Queries without drawing your attention to it.
This is called Name Auto Correct or Name Auto Corrupt if you don't actually know how it works. Best to turn off this "feature" and turn it on specifically if you want its help. This is a dangerous "feature".
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:53
Joined
May 7, 2009
Messages
19,169

Attachments

  • DATA.accdb
    800 KB · Views: 296

Cotswold

Active member
Local time
Today, 16:53
Joined
Dec 31, 2020
Messages
521
@Pat Hartman #12: This is a dangerous "feature".
I don't see it as a bad "feature". Changing Filenames, Fieldnames, Query name, or Query content is very occasional and unusual. Often simply to improve clarity, or consistency. Whilst such changes may cause a Query or Queries fall over, the errors are clearly shown and easy to correct. Having to make the corrections can be seen a gentle reminder to give more attention to naming and content during analysis, or maybe after making a quickie amendment.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:53
Joined
Feb 19, 2002
Messages
42,971
@Cotswold,
You've been lucky if it hasn't burned you. I didn't say to not use it. I said to turn it off unless you wanted to use it.

The NAC feature does not work the way most people assume it works. For example, it does not update the affected queries/forms/reports immediately. It keeps an internal table of name changes and when you open an object the next time, which might be three weeks from now, it applies the change. You run into trouble if you change the value again before you have opened all the affected objects. So - if I am going to use it, I make the table changes, then I open every single object that could potentially have been affected or I use the Access dependencies feature to tell me which ones will require changing.

The feature does not update ANY code and I doesn't update anything in a query that was alias'd or used in a function.

But the final problem, is that it ONLY works at all if your tables are local.

Better to get a real tool that changes everything that needs changing.

Here's an article and a presentation I made on the feature.
 

Attachments

  • NameAutoCorrectDOC.zip
    81.3 KB · Views: 221
  • NameAutocorrectPPT.zip
    182.7 KB · Views: 307
Last edited:

Users who are viewing this thread

Top Bottom