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: 229
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: 8,684
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.

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

My company's website
Go to the top of the page
 
msnarayanan
post Nov 15 2017, 08:15 PM
Post#3



Posts: 229
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: 8,684
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?

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

My company's website
Go to the top of the page
 
msnarayanan
post Nov 16 2017, 02:40 AM
Post#5



Posts: 229
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: 229
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: 8,684
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.

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

My company's website
Go to the top of the page
 
projecttoday
post Nov 16 2017, 03:29 AM
Post#8


UtterAccess VIP
Posts: 8,684
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.

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

My company's website
Go to the top of the page
 
msnarayanan
post Nov 16 2017, 08:16 AM
Post#9



Posts: 229
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: 8,684
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: 10
 

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

My company's website
Go to the top of the page
 
msnarayanan
post Nov 16 2017, 07:47 PM
Post#11



Posts: 229
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: 229
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: 8,684
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?

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

My company's website
Go to the top of the page
 
msnarayanan
post Nov 18 2017, 08:02 AM
Post#14



Posts: 229
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: 8,684
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.

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

My company's website
Go to the top of the page
 
msnarayanan
post Nov 18 2017, 11:13 PM
Post#16



Posts: 229
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: 8,684
Joined: 10-February 04
From: South Charleston, WV


You're welcome.

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

My company's website
Go to the top of the page
 
msnarayanan
post Dec 5 2017, 09:05 AM
Post#18



Posts: 229
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: 8,684
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】)?

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

My company's website
Go to the top of the page
 
msnarayanan
post Dec 5 2017, 09:45 AM
Post#20



Posts: 229
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    16th December 2017 - 07:15 PM