calculations in Access using SQL

[srxhuer1]

Hi all,

I have created in Access 97 a table which looks like this:

A B

1 12
2 23
3 34
4 36
5 11
6 9
7 5
8 17
9 23
10 34
11 32
12 2
. .
. .
. .
. .
. .
What i want to do is the following:

a=(12+11+23)/3
b=(23+9+34)/3
c=(34+5+32)/3
d=(36+17+2)/3

and print the result to other records.Is it possible?Please help!!!
What should i do in order to run SQL within Access?

[JeffB]

You can use the Access query builder to create most of the queries you want, but I don't understand your logic, what are you trying to do exactly?

JeffB

[srxhuer1]

Hi JeffB,

I am trying to add the first number of the first group with the first number of the second group with the first number of the third group and so on...

The same goes with the second number of the first group with the second number of the second group and so on...

So if I have groups: (a,b,c,d) (e,f,g,h) (i,j,k,l)
then i want a new group which is calculated like this:

((a+e+i/3),(b+f+j/3),(c+g+k/3),(d+h+l/3))

What i show before is just a numerical example...
There might be 80 groups with 250 elements each...

[hspc]

Hi
here is a solution
SELECT sum(num)/ (Select count(id) from tblsum where (id-1) mod 4 =0)
FROM tblsum
where (id-1) mod 4 =0
union
SELECT sum(num)/ (Select count(id) from tblsum where (id-2) mod 4 =0)
FROM tblsum
where (id-2) mod 4 =0
union
SELECT sum(num)/ (Select count(id) from tblsum where (id-3) mod 4 =0)
FROM tblsum
where (id-3) mod 4 =0
union
SELECT sum(num)/ (Select count(id) from tblsum where (id-4) mod 4 =0)
FROM tblsum
where (id-4) mod 4 =0;

for this data :
ID num
1 12
2 23
3 34
4 36
5 11
6 9
7 5
8 17
9 23
10 34
11 32
12 2
result :
15.3333333333333
18.3333333333333
22
23.6666666666667

but i'll try to get the same result using another method .. maybe group by

[hspc]

And now
a much easier way !!!

SELECT sum(num)/count(num)
FROM tblsum
GROUP BY id mod 4
ORDER BY id mod 4;

[srxhuer1]

Hspc,

Thanks!!!I am terribly sorry that i have to ask this:Where should i compile it in order to execute?

[hspc]

Open the MS access DB
Select queries
Create query in design view
in the design view window:
right click -> SQL View

paste the code and click run (exclamation mark icon)

[srxhuer1]

hspc,is there any way we could automate this process(not using the keyboard at all)?For example if we have instead of 4 numbers to add,let's say 3000 numbers to add?How can we deal with this?

[hspc]

Hi srxhuer1
I'm not sure what do you want exactly...
Do you talk about the number of records or the interval (4)
The 2 queries I post do not depend on the number of records .. They will calculate the required values even if there are 3000 or more records..
If you want to make the interval (which is 4 now) variable..you can do this :

SELECT sum(num)/count(num)
FROM tblsum
GROUP BY id mod @int
ORDER BY id mod @int;

ms access will ask you about the value of @int each time you run the query..
I hope this can help you .. If not explain more ;)

[srxhuer1]

Hspc,I run the code but i was prompt to give manually the numbers:

ID

and

NUM

Is there any way not to give numbers manuallyby the keyboard but just by clicking the exclamationicon to be able to take these values manually.

[hspc]

It asks you about ID,NUM because seems you use different column numbers than I use.
I use ID,NUM you use A,B
change either column names or the query to fix this.

[srxhuer1]

Hspc,the problem is not the names of the columns but that the program asks to put numbers manually.For example i cannot put 3000 values manually every time.

[erickwidya]

i think what hspc means
SELECT sum(num)/count([/b]num[/b])
FROM tblsum
GROUP BY id mod 4
ORDER BY id mod 4;
change the bold part to columns that u got in ur table which is num = B, id = A, tblSum = ??? not sure what's ur table_name

[hspc]

erickwidya..Thank you for trying to clarify thinkgs :)
but the code in your post needs some re-formating :
SELECT sum(num)/count(num)
FROM tblsum
GROUP BY id mod 4
ORDER BY id mod 4;

srxhuer1 :
Did you get the point ?
let us know if you still find problems..

[erickwidya]

but the code in your post needs some re-formating :blush:
aa miss something there, i done that manual :p
i think this forum might consider using method that can change it automatically :D