Help with calculation in MS Access

[Keith Robinson]

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

[alfred]

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" <Ke**************@ntlworld.com> 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

[MacDermott]

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" <Ke**************@ntlworld.com> 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

[Keith Robinson]

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 ***

[Keith Robinson]

Hi Thanks for the reply, I will try your suggestion.

Cheers
Keith

*** Sent via Developersdex http://www.developersdex.com ***

[Keith Robinson]

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 ***

[Rick Brandt]

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

[osmethod]

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

[Keith Robinson]

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 ***