Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2008
    Posts
    15

    Unanswered: Every N records Average Query

    Hi;
    How can i get average(avg_price) each 3 records from a query..
    my table or my query fields,

    id--- price--avg_price
    1 ---- 5------null or 0
    2-----20------null or 0
    3-----50------25
    4-----30------null or 0
    5-----20------null or 0
    6-----10------20
    7-----64------null or 0
    etc..
    etc..
    My query results. I want that results..

    Thank you...
    Last edited by leexlee; 12-23-08 at 19:17.

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I don't think it's possible in a query.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Mar 2008
    Posts
    15
    Thank you for your answer,

    It could be Function.. a Function better for me

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You can do it, but it relies on your sequence of id's not being broken... You can get around this by importing the data in order into a temporary table with an autonumber column.

    So assuming we have our temp table with id, price and arbitrary_integer columns, the query would look something like this:
    Code:
    SELECT prices.id
         , prices.price
         , Iif(y.top_un % 3 = 0, y.avg_price, 0) As [avg_price]
    FROM   prices
     LEFT
      JOIN (
            SELECT Max(id) As [top_un]
                 , Avg(price) As [avg_price]
            FROM   (
                    SELECT id
                         , price
                         , Floor(arbitrary_integer - 1) / 3) As [n]
                    FROM   prices
                   ) x
            GROUP
                BY n
           ) y
        ON prices.id = y.top_un
    Last edited by gvee; 12-24-08 at 04:44.
    George
    Home | Blog

  5. #5
    Join Date
    Mar 2008
    Posts
    15
    Hi Georgev, thank you for your interest;

    im sorry but i cant work your query.. i get syntax error "Iif(y.top_un % 3..." and "group by n"

    I did make several change my query( from customer tables ) look like this;

    my query name is "prices"
    ps: avg_price field there is no table, only will be query and "linenumber" field get value from a function or i write manuel. it'is not important..

    id(autonumber)---linenumber----price----avg_price

    1------------------1------------10-------null
    3------------------2------------20-------null
    4------------------3------------30-------20
    5------------------4------------50-------null
    6------------------5------------20-------null
    8------------------6------------5--------25
    13-----------------7------------15------null
    etc...
    etc...

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Sorry, I don't have a copy of Access to toy with, but the general idea is there for someone else to translate.

    FYI, the percentage sign in y.top_un % 3 is modulus, which may have different syntax in Access.
    George
    Home | Blog

Posting Permissions

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