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: 10,272
Joined: 10-February 04
From: South Charleston, WV


There is no Sum in the query. The Sum is in the report.
Go to the top of the page
 
msnarayanan
post Dec 5 2017, 11:15 AM
Post#22



Posts: 277
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: 277
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: 10,272
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?
Go to the top of the page
 
msnarayanan
post Dec 5 2017, 12:19 PM
Post#25



Posts: 277
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: 10,272
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.
Go to the top of the page
 
msnarayanan
post Dec 5 2017, 08:29 PM
Post#27



Posts: 277
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: 10,272
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")
Go to the top of the page
 
msnarayanan
post Dec 5 2017, 09:53 PM
Post#29



Posts: 277
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: 277
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: 10,272
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.
Go to the top of the page
 
msnarayanan
post Dec 6 2017, 10:44 AM
Post#32



Posts: 277
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: 277
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: 10,272
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
 
Go to the top of the page
 
msnarayanan
post Dec 7 2017, 10:57 AM
Post#35



Posts: 277
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: 10,272
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.
Go to the top of the page
 
projecttoday
post Dec 7 2017, 03:11 PM
Post#37


UtterAccess VIP
Posts: 10,272
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: 3
 
Go to the top of the page
 
2 Pages V < 1 2


Custom Search


RSSSearch   Top   Lo-Fi    21st November 2018 - 01:52 PM