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    
 
   
msnarayanan
post Nov 15 2017, 08:49 AM
Post#1



Posts: 277
Joined: 28-May 13



dear all

I have created a report with two sub reports _- one with credits
and the other payments

the difference between total of receipts and total of paynents
is 1000

to balance the totals I have created a field and manually
entered the difference value. now the totals of credits and payments
are equal.

is it possible to arrive at the difference figure automatically.

I have created quiries for the credits and payments separately.

u
I need your help.

m s narayanan
Go to the top of the page
 
projecttoday
post Nov 15 2017, 09:27 AM
Post#2


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


Yes. There is more than one way to do this. If you have only these 2 numbers from 2 queries, you could make a form with a textbox that uses 2 Dlookups as its its recordsource: Dlookup("totalamt","creditquery") - Dlookup("totalamt","debitquery").

Another approach is to to make the calculation in the query/queries itself/themselves. This usually involves placing a minus sign or multiplying by -1 the debit field.
Go to the top of the page
 
msnarayanan
post Nov 15 2017, 08:15 PM
Post#3



Posts: 277
Joined: 28-May 13



dear mr Robert
thanks for your suggestions.

since I am not fully conversant with ms access, I request
you please to suggest how to make the form for dlook.
I think it would show the difference between the two
figuresand manual entries
are to be made. please correct me if I am wrong in making
this statement.

sir, one more clarification. whether the following formula
would work, placing it in page footer:
=iif(sum(NZ(cr_amount),)>sum(NZ(dr_amount)),sum(NZ(
cr_amount))-sum(NZ(dr_amount)),". ")

thank you

m s natayanan
Go to the top of the page
 
projecttoday
post Nov 16 2017, 01:02 AM
Post#4


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


Oh, you're making a report? Yes, you could do it in a report, but why a page footer? Page footer will get you a sum for the amounts on each page. And why all the logic? For the balance total of everything, have you tried =Sum(cr_amount)-Sum(dr_amount) in the report footer?
Go to the top of the page
 
msnarayanan
post Nov 16 2017, 02:40 AM
Post#5



Posts: 277
Joined: 28-May 13



dear mr Robert
thanks for your assistance.

but the problem is the two reports are based on two separate
queries. to form the formula both the cr_amount and dr_ampint
are required. how to get them? is there a need to have one
more query? can the new query be based on the other two
queries?

sorry for troubling you.

m s narayanan.
Go to the top of the page
 
msnarayanan
post Nov 16 2017, 02:50 AM
Post#6



Posts: 277
Joined: 28-May 13



dear mr Robert

further to my earlier post. the report has two sub reports
- one for receipts and the other for payments. this is
just for info.

m s narayanan
Go to the top of the page
 
projecttoday
post Nov 16 2017, 03:24 AM
Post#7


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


If I understand you correctly you should be able to put a textbox in the report footer of the credits report with =Sum(cr_amout) in it. This gives you the total credits. Put a textbox in the report footer of the debits report with =Sum(dr_amout) in it. This gives you the total debits. Then in the report footer section of the main report put a textbox with debit textbox - credit textbox.
Go to the top of the page
 
projecttoday
post Nov 16 2017, 03:29 AM
Post#8


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


I think the reference is Me.subreportname.Report.controlname. With the sub report's name replacing subreportname and the textbox name of the total replacing controlname.
Go to the top of the page
 
msnarayanan
post Nov 16 2017, 08:16 AM
Post#9



Posts: 277
Joined: 28-May 13



dear mr Robert
sorry sir
I am unable to understand the message.
please elaborate in simple terms.

I am ignorant of technical terms.

thank you

m s narayanan
Go to the top of the page
 
projecttoday
post Nov 16 2017, 09:12 AM
Post#10


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


I created a demo for you. Open Report1 with Print Preview.
Attached File(s)
Attached File  DBCRdb.zip ( 27.42K )Number of downloads: 11
 
Go to the top of the page
 
msnarayanan
post Nov 16 2017, 07:47 PM
Post#11



Posts: 277
Joined: 28-May 13



dear mr Robert

I shall download the file and follow the method.
shall keep you posted.
thanks
m s narayanan
Go to the top of the page
 
msnarayanan
post Nov 18 2017, 05:24 AM
Post#12



Posts: 277
Joined: 28-May 13



dear mr Robert
I have followed your demo and it works well.
but I could not play any trick to balance the two. totals
automatically
..for instance
credit debit
200. 500
400 700
bal. 600
total 1200 1200

as a temporary measure I have created necessary field in the
table and entered the figure manually. is there a solution for this.
if unbound formula is to be done,. where should I place
the formula.

thanks

m snarayanan
Go to the top of the page
 
projecttoday
post Nov 18 2017, 07:44 AM
Post#13


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


Are you saying the balance should be 0? So if you make an entry on another screen and re-run the report, it will balance? You want an easier way?
Go to the top of the page
 
msnarayanan
post Nov 18 2017, 08:02 AM
Post#14



Posts: 277
Joined: 28-May 13



dear mr Robert
yes. I want to have an easier way.

m s narayanan
Go to the top of the page
 
projecttoday
post Nov 18 2017, 06:43 PM
Post#15


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


Can you envision how this will work? I mean specifically. Remember this is not a spreadsheet. So how you go about it depends on your preferences. Obviously there will be a form for making the new entries. So maybe you could do a main form with the report and the aforementioned data entry form as subforms. When you make your entry then the report is refreshed. I think that might be clunky, though (haven't tried it). Maybe the data entry form and a reporting form instead of a reporting report on a main form.
Go to the top of the page
 
msnarayanan
post Nov 18 2017, 11:13 PM
Post#16



Posts: 277
Joined: 28-May 13



dear mr Robert

your statement "this is not a spreadsheet"
clarifies all my doubts.

finally it is assumed some entries are to be made
to get the desired output.

thanks for your throughout.

m s narayanan
Go to the top of the page
 
projecttoday
post Nov 19 2017, 05:50 AM
Post#17


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


You're welcome.
Go to the top of the page
 
msnarayanan
post Dec 5 2017, 09:05 AM
Post#18



Posts: 277
Joined: 28-May 13



Dear sir

As I have already mentioned I find out the difference
By creating reports and enter in a suitable field especially created
For this purpose.

I. Tried to create a query to find out the difference and
Post it automatically in the concerned field.
The formula I used as under

=Sum([dr_amount】)-sum(【cr_amount】).
This did not work.
If unbound control is to be made for this purpose,
Please help me how and where to create it.

Thanks

M S Natayanan
Go to the top of the page
 
projecttoday
post Dec 5 2017, 09:31 AM
Post#19


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


Where is this control? What is the value of Sum([dr_amount】)? What is the value of Sum([cr_amount】)?
Go to the top of the page
 
msnarayanan
post Dec 5 2017, 09:45 AM
Post#20



Posts: 277
Joined: 28-May 13



Dear sir

They are in two queries - one for dr_amount an the other
For cr_ amount.

M S Narayanan
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search
RSSSearch   Top   Lo-Fi    21st October 2018 - 05:06 AM