UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V < 1 2  (Go to first unread post)
   Reply to this topicStart new topic
> Balancing The Totals, Access 2013    
 
   
projecttoday
post Dec 5 2017, 10:31 AM
Post#21


UtterAccess VIP
Posts: 8,679
Joined: 10-February 04
From: South Charleston, WV


There is no Sum in the query. The Sum is in the report.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
msnarayanan
post Dec 5 2017, 11:15 AM
Post#22



Posts: 229
Joined: 28-May 13



Dear sir
Though your post appears in the list, it is not to be found
When scroll down.

Please repost it.

M S Narayanan
Go to the top of the page
 
msnarayanan
post Dec 5 2017, 11:23 AM
Post#23



Posts: 229
Joined: 28-May 13



Dear sir

I could find your posting in the second page.

The sum amounts are in report and the reports are
Based on the queries. Sorry I could not explain
Properly.

M S Narayanan
Go to the top of the page
 
projecttoday
post Dec 5 2017, 11:52 AM
Post#24


UtterAccess VIP
Posts: 8,679
Joined: 10-February 04
From: South Charleston, WV


Yes. Why put the Sum in the query when there is no need for the sum in the query?

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
msnarayanan
post Dec 5 2017, 12:19 PM
Post#25



Posts: 229
Joined: 28-May 13



Dear sir

I followed trial and error method.

Please guide me what should I do.

Sorry for the trouble.

M S Narayanan
Go to the top of the page
 
projecttoday
post Dec 5 2017, 12:39 PM
Post#26


UtterAccess VIP
Posts: 8,679
Joined: 10-February 04
From: South Charleston, WV


You have not explained what you have tried to do and why the result is not as expected.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
msnarayanan
post Dec 5 2017, 08:29 PM
Post#27



Posts: 229
Joined: 28-May 13



Dear sir
I summarise below the procedure I followed.

Created the following tables:
1. Account head table
Fields: sl no., Account head, code, re_pa (for
Receipts and payments), in_ex (for income and expenditure.

2. Receipt table
Fields :. Date, account head, Dr amount, remarks

3. Payment table
Fields : date, account head, CR amount, remarks

Queries:
1. Payment query for filtering re_pa field and CR amount
2 receipt query for filtering re_pay and Dr amount
3 income query for filtering in_ex field and Dr amount
4 expenditure query for filtering in_ex and Dr amount.

Reports
Based on the queries I created reports/subreports etc.
Up to this level I was able to get the desired output.

The totals of income and expenditure and receipt and payments
Should be equalled. To do this I have created fields
And enter the difference amount manually. This works fine.

I tried to get the difference automatically by creating a
Query (fields from receipts and payment tables)
It is where I am stuck.



I think I have explained the sequences that would help you
Guiding me. If you wish I shall send the dB file.

Thanks

M S Narayanan


Go to the top of the page
 
projecttoday
post Dec 5 2017, 09:20 PM
Post#28


UtterAccess VIP
Posts: 8,679
Joined: 10-February 04
From: South Charleston, WV


I think if you just want the balance in a control (textbox) on a form you should be using Dsum instead of Sum. So for the control source

=Dsum("DR_amount","DR_query") - Dsum("CR_amount","CR_query")

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
msnarayanan
post Dec 5 2017, 09:53 PM
Post#29



Posts: 229
Joined: 28-May 13



Dear sir

I shall try and let you posted with the result in due course.

M S Narayanan
Go to the top of the page
 
msnarayanan
post Dec 6 2017, 12:49 AM
Post#30



Posts: 229
Joined: 28-May 13



dear sir
i tried dsum formula but unfortunately it returns an error. it may be
due to some procedures i explained in my earlier post.

for easy reference, i am attaching a file so that you will be able to
rectify it.

you will notice in the "receipts and payments"report, the last item
on the rightside (just above the totals), a sum of 1596 is appearing.
this amount i arrived at viewing the report and finding out the
difference and then entered in a field in payment table.

all i wanted was that this could be done automatically.

I am very sorry for taking your valuable time.

Thanks

M S NarayananAttached File  mff_17.zip ( 88.02K )Number of downloads: 7


Go to the top of the page
 
projecttoday
post Dec 6 2017, 08:20 AM
Post#31


UtterAccess VIP
Posts: 8,679
Joined: 10-February 04
From: South Charleston, WV


I downloaded your database and created a new form with the 2 subforms on it. I put a sum control in the footer of each of the subforms. I tried to reference these 2 controls in the main form but was unsuccessful. I am pretty sure this can be done but I couldn't figure it out. So I put a text box with the following:

=DSum("dr_amount","Receipts Table")-DSum("cr_amount","Payment Table")

and this appears to work. If I figure out the reference I will post back.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
msnarayanan
post Dec 6 2017, 10:44 AM
Post#32



Posts: 229
Joined: 28-May 13



Dear sir
Thank you very much for your kind help. It is 9:30 p.m. now.
I shall go through the forms tomorrow and let you know
The outcome.
May I be helpful to you in regard to reference for control.
Please let me know the details if at all you need it.

Yours
M S Narauanan
Go to the top of the page
 
msnarayanan
post Dec 6 2017, 11:44 AM
Post#33



Posts: 229
Joined: 28-May 13



Dear sir
Further to my earlier post, I could not find any
Attachment to your posting. Probably you would
Send it after locating the reference.

M S Narayanan
Go to the top of the page
 
projecttoday
post Dec 6 2017, 05:12 PM
Post#34


UtterAccess VIP
Posts: 8,679
Joined: 10-February 04
From: South Charleston, WV


I did not put in an attachment. All you need is that code. But here is my test anyway.
Attached File(s)
Attached File  subformtest.zip ( 41.09K )Number of downloads: 3
 

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
msnarayanan
post Dec 7 2017, 10:57 AM
Post#35



Posts: 229
Joined: 28-May 13



Dear sir
Thanks for your guidance. The forms work wonders.
I can find out the "difference" instantly. But it is not
Featuring in the report. I think some steps are not
Executed by me.

Sir, will it be possible to make the "difference" amount
Appear in the report directly by using the formula:
=Sum(if([acctype"=*"re",dr_amount])-sum(if([acctype=pa])
Cr_amount))
Thanks

M S. Narayanan
Go to the top of the page
 
projecttoday
post Dec 7 2017, 12:41 PM
Post#36


UtterAccess VIP
Posts: 8,679
Joined: 10-February 04
From: South Charleston, WV


I have found out in another thread how to reference the amounts on the subforms directly. I have modified the database. Unfortunately, I can't get at it right now for posting due to that computer not having internet and this computer having no Access. By I really think this is a better approach. See if you can change it yourself by following the example given by Bruce in post #4 here.

Also, did you try changing your subforms to datasheet format? This might make your entry easier (as you requested.)

I don't now what you mean by report. This is a form.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
projecttoday
post Dec 7 2017, 03:11 PM
Post#37


UtterAccess VIP
Posts: 8,679
Joined: 10-February 04
From: South Charleston, WV


Attached is the is the updated db.
Attached File(s)
Attached File  subformtest.zip ( 53.68K )Number of downloads: 1
 

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
2 Pages V < 1 2


Custom Search
RSSSearch   Top   Lo-Fi    13th December 2017 - 07:28 PM