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
> Formula For Arriving Certain Values, Access 2016    
 
   
projecttoday
post Jun 22 2020, 05:41 AM
Post#21


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


In the unbound control.

Explain:

CODE
SELECT [acc_master table].acc_code, [acc_master table].acc_point, [acc_master table].acc_name, [trans table].credit_amount
FROM [trans table] INNER JOIN [acc_master table] ON [trans table].acc_name = [acc_master table].acc_name
WHERE ((([acc_master table].acc_point)="CTR"))
ORDER BY [acc_master table].acc_code;


CODE
SELECT [acc_master table].acc_code, [acc_master table].acc_point, [acc_master table].acc_name, [trans table].credit_amount
FROM [trans table] INNER JOIN [acc_master table] ON [trans table].acc_name = [acc_master table].acc_name
WHERE ((([acc_master table].acc_point)="CTR"))
ORDER BY [acc_master table].acc_code;

--------------------
Robert Crouser
Go to the top of the page
 
msnarayanan
post Jun 22 2020, 06:15 AM
Post#22



Posts: 343
Joined: 28-May 13



Dear sir
Thanks for your mail.

I am not so conversant with coding. But definitely follow the instructions given by you.

Will this method automatically take the gross profit.
In the code there is no mention about this. Sorry if I misunderstood.

One more point sir. The coding portion is very light for viewing.

I will get back to you as soon as possible. I fear it may take more time as I am a beginner.

Thanks

m S Narayanan
Go to the top of the page
 
projecttoday
post Jun 22 2020, 06:28 AM
Post#23


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


What happens if you put a textbox on your form with =Sum(credit_amount) as the control source?

--------------------
Robert Crouser
Go to the top of the page
 
projecttoday
post Jun 22 2020, 06:36 AM
Post#24


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


Also, this textbox should be in the footer section of the form.

--------------------
Robert Crouser
Go to the top of the page
 
msnarayanan
post Jun 22 2020, 07:22 AM
Post#25



Posts: 343
Joined: 28-May 13



dear sir

Thanks for your clarifications.

I shall revert back when done = but it will take some more time since i am a newbie to this sosrt of things.

Kindly bear with me.

M S Narayanan
Go to the top of the page
 
projecttoday
post Jun 22 2020, 09:31 AM
Post#26


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


It shouldn't take you more than a minute to put that textbox on your form. I think you can put it anyplace, but the header or footer is a good place for it.

--------------------
Robert Crouser
Go to the top of the page
 
msnarayanan
post Jun 23 2020, 12:02 AM
Post#27



Posts: 343
Joined: 28-May 13



Dear sir

I will do it today.

Which should I try. The two comings or the one line text?
Where sould they be placed - form or report?

Thanks

M S Narayanan

Go to the top of the page
 
projecttoday
post Jun 23 2020, 12:13 AM
Post#28


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


Use a report if you want to print it. If not, use a form.

You must make sure the sum of your credits is correct and the sum of your debits is correct before you can calculate the balance. So do them individually first.

--------------------
Robert Crouser
Go to the top of the page
 
msnarayanan
post Jun 23 2020, 08:37 AM
Post#29



Posts: 343
Joined: 28-May 13



Dear sir

I tried to follow the suggestion made by you. I could not complete it as I could not find the correct form.

I followed your suggestion by creating a fresh form and created unbound fiels at the form footer. They are as follows:
For total credits: =sum(credit_amount)
For total debits: =sum(debit_amount
For gross profit: =sum(credit_aount)-sum(deit_aount)



I have tested by entering sample data the results shown in the form is correct.

but these figures do not feature in the table. If this is solved I can create a report based on the table.

How can I do it. Can I have these unbound fields in the report directly?

Thanks.

m SvNarayanan

Thanks
Go to the top of the page
 
projecttoday
post Jun 23 2020, 09:23 AM
Post#30


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


If the figures are in the table then they should display on the form without entry. What kind of form is it (continuous, single record) ? What is the SQL of the form?

A report is the same.

--------------------
Robert Crouser
Go to the top of the page
 
msnarayanan
post Jun 24 2020, 12:58 AM
Post#31



Posts: 343
Joined: 28-May 13



dear sir

it is a continuoous form. it shows totals even without any entry.
sir, i could not get the SQL. instead i am sending the db file.
i tried to create a query and it returns with some message.

thank you

M S Narayanan
Attached File(s)
Attached File  grossprofit.zip ( 41.88K )Number of downloads: 1
 
Go to the top of the page
 
projecttoday
post Jun 24 2020, 01:16 AM
Post#32


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


The form looks okay to me. It totals the values in the table. Isn't that what you wanted?

--------------------
Robert Crouser
Go to the top of the page
 
msnarayanan
post Jun 24 2020, 01:44 AM
Post#33



Posts: 343
Joined: 28-May 13



Dear sir

It is true it shows the figures that I wanted.

But the are not found in the table. If they are sown I may create a report.

To get this should I enter the formula in report. Query could not be created.

Your suggestions please.

Sorry I am taking much of your time,

m S Narayanan
Go to the top of the page
 
projecttoday
post Jun 24 2020, 07:03 AM
Post#34


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


I'm sorry I don't understand. Where do these numbers come from? At some point somebody has to enter the numbers into a table. Or you have to import them from another system.

--------------------
Robert Crouser
Go to the top of the page
 
tina t
post Jun 24 2020, 07:43 AM
Post#35



Posts: 6,664
Joined: 11-November 10
From: SoCal, USA


QUOTE
It is true it shows the figures that I wanted.

But the are not found in the table. If they are sown I may create a report.

To get this should I enter the formula in report.

hi, PMFJI. are you saying that the Sum() values are not being stored in a table? if so, that's correct. when you use expressions such as

For total credits: =sum(credit_amount)
For total debits: =sum(debit_amount)
For gross profit: =sum(credit_amount)-sum(debit_amount)

in the ControlSource property of controls on a form, the values are displayed, but they are not stored in any table. that's what a calculated value is: a value that is calculated at runtime, using the raw data that is stored in a table.

so to answer your question: yes, you can use the same formula (expression) in unbound textbox controls in a report, just like you did in the form.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
projecttoday
post Jun 24 2020, 10:33 AM
Post#36


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


Oh, so that's what he/she meant. No, the sum won't get saved to any table that way.

And the question is why does the person entering the credits and the debits need to see this sum?

Usually sums go on a report. A report can calculate a sum. When necessary, the sum can be stored in a table.

Don't expect to do everything on one screen.

--------------------
Robert Crouser
Go to the top of the page
 
msnarayanan
post Jun 24 2020, 11:14 AM
Post#37



Posts: 343
Joined: 28-May 13



Dear sirs

Thanks to all of you for having helped me in solving my problems/doubts.

M S Narayanan
Go to the top of the page
 
projecttoday
post Jun 24 2020, 11:30 AM
Post#38


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


You're welcome.

--------------------
Robert Crouser
Go to the top of the page
 
2 Pages V < 1 2


Custom Search


RSSSearch   Top   Lo-Fi    2nd July 2020 - 11:04 AM