455,479 Members | 1,601 Online
Need help? Post your question and get tips & solutions from a community of 455,479 IT Pros & Developers. It's quick & easy.

# Help with calculation in MS Access

 P: n/a Hi I wonder if anyone could help me with my grand-daughters home work? We are using a form to add data about personnel who work for our imaginary company. Each record has the employee's ID, Name, Job title, and the amount on money we have supposed to have given him/her in the current month. Is there a way of showing (in a separate box), how much the running total would be, i.e how much money he would have received from the company in the last three months? Cheers Keith Nov 13 '05 #1
8 Replies

 P: n/a pUT FOLLOWING IN QUERY (or use a report to get running totals) SELECT t1.KeyField, (SELECT Sum(t2.Amount) FROM YourTable AS t2 WHERE t2.KeyField<=t1.KeyField) AS RunBal FROM YourTable AS t1 ORDER BY t1.KeyField; Alfred "Keith Robinson" wrote in message news:W3***************@newsfe1-win.ntli.net... Hi I wonder if anyone could help me with my grand-daughters home work? We are using a form to add data about personnel who work for our imaginary company. Each record has the employee's ID, Name, Job title, and the amount on money we have supposed to have given him/her in the current month. Is there a way of showing (in a separate box), how much the running total would be, i.e how much money he would have received from the company in the last three months? Cheers Keith Nov 13 '05 #2

 P: n/a Are you really only entering data for one month? In that case, your "running total" would simply be 3 * the amount for the current month. Assuming that the field in your table is named Payment, you could set the ControlSource of your separate textbox like this: = [Payment] * 3 If you're actually entering records for several months, I'd recommend splitting your table into two - one for Employees (ID, Name, Job), and one for Payments (ID, PayDate, Amount). Then we'd need to know why you said 3 months. Do you really only want to know about the last 3 months? Or do you only have 3 months of data, and what you want is the total of whatever you have? Perhaps your grand-daughter can answer some of these questions. "Keith Robinson" wrote in message news:W3***************@newsfe1-win.ntli.net... Hi I wonder if anyone could help me with my grand-daughters home work? We are using a form to add data about personnel who work for our imaginary company. Each record has the employee's ID, Name, Job title, and the amount on money we have supposed to have given him/her in the current month. Is there a way of showing (in a separate box), how much the running total would be, i.e how much money he would have received from the company in the last three months? Cheers Keith Nov 13 '05 #3

 P: n/a Hi Thanks for your help. The three months was only an example, we want a continuing running total. In the Forms control box I did add =[Payment]*3 this did not work. Could I not do this in a query? again I tried and failed, I'm not great at this I'm afraid. Thanks again Keith *** Sent via Developersdex http://www.developersdex.com *** Nov 13 '05 #4

 P: n/a Hi Thanks for the reply, I will try your suggestion. Cheers Keith *** Sent via Developersdex http://www.developersdex.com *** Nov 13 '05 #5

 P: n/a Hi Sorry I'm not explaining myself very well with this problem. But basically I have a table with a field named "Income". Each time we pay an employee money, the amount is entered into the "Income" field. which could look some thing like this.. Income 500 300 1000 150 450 If this column was added up it would come to 2400 We have also created a Form. In this there are two text boxes, one to add data i.e the amount money I am giving the employee at this time (this will transfer to the Income field in our table). in the other text box (named Total Income) I would like the total income we have give to this employee thoughout the year to appear. So for example If I give an employee £100, I would then like the Total Income text box to change from 2400 (as above) to 2500. Maybe this is not possible. Thanks Keith *** Sent via Developersdex http://www.developersdex.com *** Nov 13 '05 #6

 P: n/a Keith Robinson wrote: Hi Sorry I'm not explaining myself very well with this problem. But basically I have a table with a field named "Income". Each time we pay an employee money, the amount is entered into the "Income" field. which could look some thing like this.. Income 500 300 1000 150 450 If this column was added up it would come to 2400 We have also created a Form. In this there are two text boxes, one to add data i.e the amount money I am giving the employee at this time (this will transfer to the Income field in our table). in the other text box (named Total Income) I would like the total income we have give to this employee thoughout the year to appear. So for example If I give an employee £100, I would then like the Total Income text box to change from 2400 (as above) to 2500. Maybe this is not possible. Check help file for the DSum() function. =DSum("Income", "YourTableName", "EmployeeID = " & Me.EmployeeID & " AND PaymentDate > #" & DateSerial(Year(Date())-1, 12, 31) & "#") Expression explained: Find the sum of the field [Income] from the table "YourTableName" for the employee currently displayed on the form and where the date of payment is greater than December 31st of last year. Now, I made some assumptions in the example. One is that you have a field that identifies the employee. I named mine EmployeeID and the expression above assumes this is a numeric value. If it were text I would need single quote delimiters around that criteria value similar to the way I used # delimiters around the date criteria. Much of the difficulty of setting up a DSum() expression like above is getting all of the delimiters and quote-breaks correct. If you have trouble post back. -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com Nov 13 '05 #7

 P: n/a Keeping things simple for you at the moment: Form bound to Income table. Income table contains "Income" Field. TextBox named Text1 on Form. In the control source of Text1 put: =Sum([Income]) In the "AfterUpdate" event of the textbox where you enter the money, put the following in the sub Me.requery osmethod Nov 13 '05 #8

 P: n/a Hi again, thanks that worked great. Each time I enter a figure the total income appears in the text box, which is fanstastic, but is there anyway that the text box will calculate just 28 days then reset, so it will then begin to calulate the next 28 days? or am I now asking too much. Thanks for all your help Cheers Keith *** Sent via Developersdex http://www.developersdex.com *** Nov 15 '05 #9

### This discussion thread is closed

Replies have been disabled for this discussion.