# Thread: Every N records Average Query

1. Registered User
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 18:17.

2. L33t Helpa Munky
Join Date
Nov 2007
Location
Posts
4,049
I don't think it's possible in a query.

3. Registered User
Join Date
Mar 2008
Posts
15

It could be Function.. a Function better for me

4. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
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 03:44.

5. Registered User
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. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
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.

#### Posting Permissions

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