Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Problem with query (Acc97 sr2)

    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"
    "Heading for the deep end"

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Problem with query (Acc97 sr2)

    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(....
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Problem with query (Acc97 sr2)

    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.
    "Heading for the deep end"

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Problem with query (Acc97 sr2)

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

  5. #5
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Problem with query (Acc97 sr2)

    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.
    "Heading for the deep end"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •