Calculations on a form

[Nona]

I need help again - this time with calculations on a form. Using Access 2000,
I have a continuous form on which a supervisor would assign daily hours to
each of several therapists who work with about 50 patients.

After entering the therapists’ weekly assigned work hours for each patient,
I want the form to total each therapist’s hours for all patients. This works
with a simple arithmetical formula that is repeated for each therapist.
=[ThMQA]+[FrMQA]+[SaMQA]+[SuMQA]+[MoMQA]+[TuMQA]+[WeMQA]

Can I use an expression to get these weekly totals so that I will have a
field to use to get a sum in the footer? I want the form footer to total the
hours for the therapist for all the patients assigned to him/her. The footer
formula I have returns the totals from the first record (patient) but doesn’t
include the other records (patients).
=Sum([ThHrsMQS]+[FrHrsMQS]+[SaHrsMQS]+[SuHrsMQS]+[MoHrsMQS]+[TuHrsMQS]+[WeHrsMQS])

Is there a better way to accomplish this? I want this in a form, but I know
I can't use expressions in the query supporting the form. That's why I'm
struggling with this--the query would work easily if I could put the
calculations there.

Another frustrating thing: I have been through hours and hours and
countless trials trying to get these calculations on this form to work. Now
it gives me an error saying the network connection may have been lost. (I had
copied and pasted a field on the form.) I refreshed the link, but still get
the message. This happened before, and I thought the form must have been
corrupted, so I started completely over, but after working on the form for
some time, that error popped up again. Any ideas?

I will appreciate some expert help!

 

[John W. Vinson]

I know I can't use expressions in the query supporting the form.

Why ever not?

You can't update the calculated field, but it won't prevent the updating of
other fields, and it can be summed on the form.

 

[Gina Whipp]

Answers in-line...

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

I need help again - this time with calculations on a form. Using Access
2000,
I have a continuous form on which a supervisor would assign daily hours to
each of several therapists who work with about 50 patients.

After entering the therapists' weekly assigned work hours for each
patient,
I want the form to total each therapist's hours for all patients. This
works
with a simple arithmetical formula that is repeated for each therapist.
=[ThMQA]+[FrMQA]+[SaMQA]+[SuMQA]+[MoMQA]+[TuMQA]+[WeMQA]

Can I use an expression to get these weekly totals so that I will have a
field to use to get a sum in the footer? I want the form footer to total
the
hours for the therapist for all the patients assigned to him/her. The
footer
formula I have returns the totals from the first record (patient) but
doesn't
include the other records (patients).
=Sum([ThHrsMQS]+[FrHrsMQS]+[SaHrsMQS]+[SuHrsMQS]+[MoHrsMQS]+[TuHrsMQS]+[WeHrsMQS])

Is there a better way to accomplish this? I want this in a form, but I
know
I can't use expressions in the query supporting the form. That's why I'm
struggling with this--the query would work easily if I could put the
calculations there.

**** Why not? Does something happen to the query when you do this?

Another frustrating thing: I have been through hours and hours and
countless trials trying to get these calculations on this form to work.
Now
it gives me an error saying the network connection may have been lost. (I
had
copied and pasted a field on the form.) I refreshed the link, but still
get
the message. This happened before, and I thought the form must have been
corrupted, so I started completely over, but after working on the form for
some time, that error popped up again. Any ideas?

**** When you get that message do you try to open the table? If you
experience that frequently you might want to have your network connection
looked at because that can lead to corruption and then you will really be
upset.

 

[Nona]

Will the form allow data entry with calculated expressions in the query
supporting the form? That's been my problem - if I add an expression, the
query or the form no longer accepts data entries.

I sure would like to be able to use them in a form. Am I doing something
wrong?

 

[John W. Vinson]

Will the form allow data entry with calculated expressions in the query
supporting the form? That's been my problem - if I add an expression, the
query or the form no longer accepts data entries.

Does for me.

I sure would like to be able to use them in a form. Am I doing something
wrong?

If you're doing any Totals - i.e. if there is a Group By clause in the query -
then no, it won't be updateable; but having just a calculated field does not
interfere with updating the *other* fields. It's a very common technique in
fact - e.g. a sales table with fields UnitPrice and Quantity might have a
calculated field ExtendedPrice: [UnitPrice] * [Quantity].

If you're seeing something different please post the SQL view of your query.

 

[Nona]

Thank you both, Gina and John. You provided valuable help, and I sincerely
appreciate it. John, you were right. I was confusing the "Group By" with the
expressions. Being able to use the expressions solved my problems.

As to the problem with the error messages, I was actually working off the
network and using a dummy table related to the FE. I think that version was
corruped, so I exported the query to a new db copy and haven't had that error
message again.

Thanks to you both!
--
Nona

Answers in-line...

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

I need help again - this time with calculations on a form. Using Access
2000,
I have a continuous form on which a supervisor would assign daily hours to
each of several therapists who work with about 50 patients.

After entering the therapists' weekly assigned work hours for each
patient,
I want the form to total each therapist's hours for all patients. This
works
with a simple arithmetical formula that is repeated for each therapist.
=[ThMQA]+[FrMQA]+[SaMQA]+[SuMQA]+[MoMQA]+[TuMQA]+[WeMQA]

Can I use an expression to get these weekly totals so that I will have a
field to use to get a sum in the footer? I want the form footer to total
the
hours for the therapist for all the patients assigned to him/her. The
footer
formula I have returns the totals from the first record (patient) but
doesn't
include the other records (patients).
=Sum([ThHrsMQS]+[FrHrsMQS]+[SaHrsMQS]+[SuHrsMQS]+[MoHrsMQS]+[TuHrsMQS]+[WeHrsMQS])

Is there a better way to accomplish this? I want this in a form, but I
know
I can't use expressions in the query supporting the form. That's why I'm
struggling with this--the query would work easily if I could put the
calculations there.

**** Why not? Does something happen to the query when you do this?

Another frustrating thing: I have been through hours and hours and
countless trials trying to get these calculations on this form to work.
Now
it gives me an error saying the network connection may have been lost. (I
had
copied and pasted a field on the form.) I refreshed the link, but still
get
the message. This happened before, and I thought the form must have been
corrupted, so I started completely over, but after working on the form for
some time, that error popped up again. Any ideas?

**** When you get that message do you try to open the table? If you
experience that frequently you might want to have your network connection
looked at because that can lead to corruption and then you will really be
upset.

I will appreciate some expert help!