Problem with query (Acc97 sr2)

Topic

New Reply

I am attempting the following update query and get the resulting error. What is the problem?

UPDATE HoursTable SET HoursTable.JobPercent = Sum([Original]![pdone])/Count([Original]![pdone]) WITH OWNERACCESS OPTION;

I get a “JobPercent is not part of the aggregate function error”

Reply | Quote

Replies

you wrote:
>>UPDATE HoursTable SET HoursTable.JobPercent = Sum([Original]![pdone])/Count([Original]![pdone]) WITH OWNERACCESS OPTION;<<

To SQL, the whole equation you wrote makes no sense. What is Original!pdone, for example? I suspect you want to use the results of a subquery, which must then be in the form: (Select Sum(….

Reply | Quote

I’m sure you are right but I don’tt know how to do it.

This is what I want.

I have a table called Original that keeps details on each job.
There is a yes/no field for [Finished] and a number field [pdone] for a value showing percentage done.

I have a bar graph that shows on the main form, that shows Total percentage of current jobs finished, that gets its value from [HoursTable].[JobPercent].

The JobPercent value is Sum([Original]![pdone])/Count([Original]![pdone])WHERE[Original].[Finished]=False.

I want to be able to run an update query that calculates the above value and updates [HoursTable].[JobPercent]

I have gone this way rather than a calculated dlookup as the dlookup takes so long on our slow machines.

The coding needed will be most appreciated.

Reply | Quote

What makes you think that an update query is going to be faster than a dlookup?

Reply | Quote

I have set it up so that the form gets its data from a table, rather than calculating it on the fly. I had started with my 9 values on my main form being updated via dlookup, but this caused a significant pause in loading, so I was out to populate a table with values being updated at significant events other than at loadup time so that the main form loaded without a noticable pause.

I have actually achieved what I wanted, thanks to the pointers from here and the help files.

I have set up a macro to run an update of the hours totals and the average of work done over all the current jobs, that is activated when a new job is added and when a job is closed.

My problem was not knowing how to write the update query properly so that it took values from a table that was not related to the output table and performed calculation on them before updating the output table, but as I said, I finally worked it out.

I have set a timer event to refresh the values periodically.

Reply | Quote