field data subtraction

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ptlm65 - 08 Dec 2005 14:19 GMT

OK,
I know this is simple but I am having a brain freeze today.

What I need to do is perform a simple subtraction of fields with th
sum automatically entered into a another field.

I have three tables:
tblHours Given
tblHours Served
tblHours Remaining

I need to have a form code that when I change the amount of "hour
served" it will subtract from the "hours given" and the new update
total automatically entered into the "hours remaining" Any help? Than
yo

--
ptlm65Posted from - http://www.officehelp.i

Reply to this Message

Allen Browne - 08 Dec 2005 15:14 GMT

Remove the Remaining field from your table. It is giving you maintenance
problems you don't need.

Instead, create a query that shows you this calculated field. You never have
to update it, and never have to worry about whether it is right. And you can
use the query anywhere you could have used your table, e.g. for a form,
report, ...

1. Create a query into this table.

2. In query design view, type this expression into a fresh column in the
Field row:
Remaining: [Given] - [Served]

More info in this article:
Calculated fields
at:
http://allenbrowne.com/casu-14.html

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> OK,
> I know this is simple but I am having a brain freeze today.
[quoted text clipped - 11 lines]
> total automatically entered into the "hours remaining" Any help? Thank
> you

Reply to this Message

ptlm65 - 08 Dec 2005 16:59 GMT

Thanks that worked great! I have one more delemma:
How do I create a form that allows me to enter new hours served tha
would add to the hours served field?

I thought of creating a combo box with the person's name
Then create a text field to enter the new hours served
Then a command button to execute the amount in the text box to be adde
to the existing hours remaining field which would in turn automaticall
update the total hours remaining in the query. What would the code b
for that?

Allen Browne Wrote:

> Remove the Remaining field from your table. It is giving yo
> maintenance
[quoted text clipped - 44 lines]
> > --
> > ptlm65Posted from - http://www.officehelp.in

--
ptlm65Posted from - http://www.officehelp.i

Reply to this Message

Allen Browne - 09 Dec 2005 01:21 GMT

If you trust the users, you could just let them increase the value of the
Served field from say 6 to 7 when the person works an extra hour.

I am assuming that the people serve their hours over several sessions. If
so, it makes sense to have a related table showing:
PersonID relates back to the primary key of your Person table.
DateServed the date the person served this time
HoursServed the number of hours served on this occasion.

You could then create a main form with the person's details and the hours
they are to serve, and a subform showing the hours they have served, one or
row. In the Form_Footer of the subform, add a text box with these
properties:
Control Source: =Sum([HoursServed])
Format: General Number
Name: txtTotalHoursServed
You can now see each timeslot they have served, with the total at the
bottom.

If you want to see the hours remaining, you could then add another text box
to the main form, with Control Source:
=[Given] - [NameOfYourSubformControlHere].[Form]![txtTotalHoursServed]

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Thanks that worked great! I have one more delemma:
> How do I create a form that allows me to enter new hours served that
[quoted text clipped - 47 lines]
>> > total automatically entered into the "hours remaining" Any help?
>> Thank you

Reply to this Message