summing multiles fields from one table in a form.

[Chekotah]

[AR&R Hours]+[PTO # of Hours]+[Meeting/Calls # of Hours]+[System Issues # of Hours]+[BAU Training # of Hours]+[Exception Processing Hours]+[AIG Related Activities]+[Admin Work Hours]+[Migration Hours]+[Project Hours]



Need the fastest and easiest way to auto calculate the above in a field on a form. i can get it to calculate in a query but need it to do in the form as well.

Please advise. I know that this may seem like a simple request. but everything I have tried has failed.

Thanks Chekotah.

[John_G]

Hi -

Put the calculated field in a query, and then base the form on that query instead of the table. The calculated field should update as changes are made to the other fields. Watch out for nulls, though - they can cause problems with a calculated field (check out the Nz function if this might be a problem)

HTH

John

[Chekotah]

Thank you. When creating the form from the query. It would not allow me to make any entries on the form. Am I missing something.

[John_G]

Hi -

Assuming you created and saved the query - are you able to open and update the query data in MS Access query design, i.e. outside the form? (Be careful when you try that!!). If you can update and add, then there is probably something wrong in the form.

If you can't add/update using just the query - does the query have more than one table?

[Chekotah]

Hi John. Can not create in the query either. There is only one table.

[John_G]

Strange - you should be able to. Can you post the SQL for the query (use SQL view in query design)? It's not a summation query is it? Those are not updatable.

[Chekotah]

SELECT [Earned Hours].Date, [Earned Hours].Name, [Earned Hours].[Employee ID], [Earned Hours].Department, [Earned Hours].Process, [Earned Hours].LOB, [Earned Hours].[Regular Hours], [Earned Hours].[Overtime Hours], [Earned Hours].[Core Production # of Hours], [Earned Hours].Volume, [Earned Hours].[AR&R Description], [Earned Hours].[AR&R Hours], [Earned Hours].[PTO # of Hours], [Earned Hours].[Meeting/Calls # of Hours], [Earned Hours].[System Issues # of Hours], [Earned Hours].[BAU Training # of Hours], [Earned Hours].[Exception Processing Hours], [Earned Hours].[AIG Related Activities], [Earned Hours].[Admin Work Hours], [Earned Hours].[Migration Hours], [Earned Hours].[Migration PID], [Earned Hours].[Project Hours], Sum(([AR&R Hours]+[PTO # of Hours]+[Meeting/Calls # of Hours]+[System Issues # of Hours]+[BAU Training # of Hours]+[Exception Processing Hours]+[AIG Related Activities]+[Admin Work Hours]+[Migration Hours]+[Project Hours])) AS [Total Exception Time], [Earned Hours].[Total Hours], [Earned Hours].[Comments/Explainations], [Earned Hours].[Earned hours]
FROM [Earned Hours]
GROUP BY [Earned Hours].Date, [Earned Hours].Name, [Earned Hours].[Employee ID], [Earned Hours].Department, [Earned Hours].Process, [Earned Hours].LOB, [Earned Hours].[Regular Hours], [Earned Hours].[Overtime Hours], [Earned Hours].[Core Production # of Hours], [Earned Hours].Volume, [Earned Hours].[AR&R Description], [Earned Hours].[AR&R Hours], [Earned Hours].[PTO # of Hours], [Earned Hours].[Meeting/Calls # of Hours], [Earned Hours].[System Issues # of Hours], [Earned Hours].[BAU Training # of Hours], [Earned Hours].[Exception Processing Hours], [Earned Hours].[AIG Related Activities], [Earned Hours].[Admin Work Hours], [Earned Hours].[Migration Hours], [Earned Hours].[Migration PID], [Earned Hours].[Project Hours], [Earned Hours].[Total Hours], [Earned Hours].[Comments/Explainations], [Earned Hours].[Earned hours];

[John_G]

Hi -

Your problem is the GROUP BY, which makes it a summation query. A summation query is not updatable.

Is there a reason why you are using GROUP BY?

[Chekotah]

Hi -

No reason in particular. It defaulted to it when I created the query.. Please advise what it should be. I do appreciate all the help. This project has been somewhat more complexed than I had orginally hoped. which I am sure all projects turn out that way.

[John_G]

Hi -

The query defaulted to GROUP BY because you clicked the summation button in query design. What it does is this case is combine all records where EVERY FIELD has the same value, into one record in the query. I doubt you need it; open the query in design mode, and turn the summation off (the query design row labeled "Totals" will go away). Save and run the query - it should now be updatable.

John

[Chekotah]

ok. I have removed the total field however now my calculation for the total exception time doesnt work. I need it to calculate all the below fields.

Total Exception Time: Sum([AR&R Hours]+[PTO # of Hours]+[Meeting/Calls # of Hours]+[System Issues # of Hours]+[BAU Training # of Hours]+[Exception Processing Hours]+[AIG Related Activities]+[Admin Work Hours]+[Migration Hours]+[Project Hours])

[John_G]

Hi -

Sorry, I missed that in your original SQL. The SUM function as you have it above does not work that way. If you want to get the sum of the contents of several different fields in a query, just add them:


Total Exception Time: [AR&R Hours]+[PTO # of Hours]+[Meeting/Calls # of Hours]+[System Issues # of Hours]+[BAU Training # of Hours]+[Exception Processing Hours]+[AIG Related Activities]+[Admin Work Hours]+[Migration Hours]+[Project Hours]

Note that if any of the fields you are adding together are Null, the whole total will be Null (and therefore incorrect). If this can happen, use the Nz function to set Null values to 0, for example: Nz([Exception Processing Hours], 0)

It wouldn't hurt to do that for all the fields you are adding, just in case. Each field would be in a separate Nz() function.

HTH

John

[Chekotah]

thank you, thank you , thank you so much. It worked.

[John_G]

Glad you got it working. Gook luck in your project.

John

[Chekotah]

Thank you on the luck part. I am needing it. this project opens up many questions and opportunities. I still have some things to work out on it such as user interface and tons of reports to be generated out of it.