Average value (on continuous form)

[Sniper-BoOyA-]

Good Morning,

I am working on a form which is a continuous form atm. Up to this point things are going allright, but now ive come across something that i am struggling with.

First of all, ill show you the query used for this form :

SELECT querywatergehalte.tbllabgegevens.monsternr, tblstabilisatiecilinders.*, querywatergehalte.watergeh, (([massa]/100+[watergeh])*100)/[opvinh] AS drgdchthd, tblopv.opvgew, tblopv.opvinh, ([totaaldruk]/[opvgew])*1000 AS d7, ([totaaldruk]/[opvgew])*1000 AS d8
FROM (tblstabilisatiecilinders RIGHT JOIN querywatergehalte ON tblstabilisatiecilinders.monsternr = querywatergehalte.tbllabgegevens.monsternr) LEFT JOIN tblopv ON tblstabilisatiecilinders.opvnr = tblopv.opvnr;

Ok, where were we.

Ermm..

As you can see the monsternr (sample numbers) are found in the table "labgegevens". Which prevents the user from changing or adding any of em.

So far so good,

Here's the thing. I want to calculate the everage of the value of "watergeh", " D7" and " D28"

So far so good. But here comes the tricky part.

When it comes to "watergeh" i need the average of Sample number 1 and 3, 4 and 6 and 7 and 9.

And when it comes to D7 and D28 i need the average of Sample number 1 2 3, 4 5 6 and 7 8 9.

Ive been trying to get it to work somehow, but no luck yet. Do you have any ideas?

Edit : I have added a " id" field to the table, to have at least one static reference point when it comes to filtering data. If i come across the solution, ill let you know.

Cheers!

 

[vbaInet]

Are you saying you want 7 lots of averages based on those 3 fields?

 

[Sniper-BoOyA-]

Are you saying you want 7 lots of averages based on those 3 fields?

Sorry for the late reply.

OT:

Not really .

Ive made a screenshot of the form to clear things up a bit. I had to put it in a .zip file though.

The idea is,

First i calculate the watergehalte (water density), which works fine.

In that process every Monsternr/Proefnr (sample nr) will get his watergehalte (Water Density).

What i want is to calculate the average of the Watergehalte (Water Density) where samplenr = 1 and 3, 4 and 6 , 7 and 9.

Ive got the following query which is used for an input form:

SELECT querywatergehalte.tbllabgegevens.monsternr, tblstabilisatiecilinders.*, querywatergehalte.watergeh, (([massa]/100+[watergeh])*100)/[opvinh] AS drgdchthd, tblstabilisatiecilinders.opvnr, tblopv.opvgew, tblopv.opvinh, ([totaaldruk]/[opvgew])*1000 AS d7, ([totaaldruk]/[opvgew])*1000 AS d28
FROM (tblstabilisatiecilinders RIGHT JOIN querywatergehalte ON tblstabilisatiecilinders.monsternr = querywatergehalte.tbllabgegevens.monsternr) LEFT JOIN tblopv ON tblstabilisatiecilinders.opvnr = tblopv.opvnr;

My first thought was, i had to filter the data, since its a continuous form.
Made a new form, used the Query as source.

So what i tried to do is:

watergeh1: Iif([tbllabgegevens].[monsternr]="1";watergeh;"")
 
watergeh3: Iif([tbllabgegevens].[monsternr]="3";watergeh;"")
 
watergem1: (watergeh1 + watergeh3 ) /2

Result:

Watergeh1 --> Good
Watergeh3 --> Good
watergem --> Not good, it simply repeated Watergeh1 and 3. And it did not add up.

So my question (for now) is to get an everage of Watergehalte (Water Density) where Monsternr (Samplenr) = 1 and 3.

Again, the screenshot might clear things up a bit.

Cheers!

 

[vbaInet]

What i want is to calculate the average of the Watergehalte (Water Density) where samplenr = 1 and 3, 4 and 6 , 7 and 9.

Ok, so this is the bit I don't understand. Shouldn't it where samplenr = 1, 3, 4, 6 and 7? There's a difference. If you say, 1 and 3, 4 and 6, 7, it sounds as though you want an average for 1 and 3 first, then 4 and 6, then a third for 7.

So do you mean get the average of Watergehalte where samplenr is 1, 3, 4, 6 and 7, in other words 1 OR 3 OR 4 OR 6 OR 7?

 

[Sniper-BoOyA-]

Ok, so this is the bit I don't understand. Shouldn't it where samplenr = 1, 3, 4, 6 and 7? There's a difference. If you say, 1 and 3, 4 and 6, 7, it sounds as though you want an average for 1 and 3 first, then 4 and 6, then a third for 7.

So do you mean get the average of Watergehalte where samplenr is 1, 3, 4, 6 and 7, in other words 1 OR 3 OR 4 OR 6 OR 7?

You can see it as groups.

Group 1 = samplenr 1,3
Group 2 = samplenr 4,6
Group 3 = samplenr 7,9

So what i would like to do is get the average of 1,3 and use that value for group 1. etc..

As i mentioned in the reply above, my first thought was to filter the data. And eventually do

( watergeh1+watergeh3 ) / 2 to get the everage.

But what i get is:

When samplenr = 1 --> watergeh = 8.36257487 --> Average = 4.181287
When samplenr = 3 --> watergeh = 7.93494582 --> Average = 3.967472

Which means they dont add up at all.

On samplenr =1 it seems like it does (8.36257487+0) / 2
On samplenr =3 it seems like it does (0+7.93494582) / 2

 

[vbaInet]

Ok, here's Group 1 average:

Gp1_Avg: Sum(IIF([Samplenr] In (1, 3), [WaterField], Null)) / Count(IIF([Samplenr] In (1, 3), 1, Null))

Repeat the same for the other groups as alias fields.

 

[Sniper-BoOyA-]

Ok, here's Group 1 average:

Gp1_Avg: Sum(IIF([Samplenr] In (1, 3), [WaterField], Null)) / Count(IIF([Samplenr] In (1, 3), 1, Null))

Repeat the same for the other groups as alias fields.

Thanks m8. Ill test it straight away!

Cheers!

 

[Sniper-BoOyA-]

Hmm getting an error that Monsternr (samplenr) is not part of a statistic function.

Which means there might be a error in the selection query right ?

SELECT querystabilisatieinvoer.tbllabgegevens.monsternr, querystabilisatieinvoer.*, IIf(tbllabgegevens.monsternr="1",[watergeh],"") AS watergeh1, IIf(tbllabgegevens.monsternr="3",[watergeh],"") AS watergeh3, [B]Sum(IIf([querystabilisatieinvoer].[tbllabgegevens].[monsternr] In (1.3),[watergeh],Null))/Count(IIf([querystabilisatieinvoer].[tbllabgegevens].[monsternr] In (1.3),1,Null)) AS Gp1_Avg[/B], IIf(tbllabgegevens.monsternr="4",[watergeh],"") AS watergeh4, IIf(tbllabgegevens.monsternr="6",[watergeh],"") AS watergeh6;

 

[vbaInet]

What is this?

[querystabilisatieinvoer].[tbllabgegevens].[monsternr]

Since when have you seen a field referenced this way in a query?

 

[Sniper-BoOyA-]

Good Morning,

Just wanted to keep you updated on this matter.

Ive gotten to the point where i can calculate the Sum where monsternr="1" or monsternr="3" by doing the following:

Sum13: IIf([querystabilisatieinvoer].[tblwatergehalte].[monsternr]="1" Or [tblwatergehalte].[monsternr]="3";(SELECT SUM([watergeh]) FROM querywatergehalte WHERE  [tblwatergehalte].[monsternr]="1" OR [tblwatergehalte].[monsternr]="3");"")

I didt the same for 4 and 6, and 7 and 9.

Then i devided the sum by 2 :

avg13: IIf(querystabilisatieinvoer.tblwatergehalte.monsternr="1" Or tblwatergehalte.monsternr="3";[Sum13]/2;"")

Again, did the same for the other 2 couples.

Finally i wanted to show the matching averages on the form.

So i did :

avgwatergeh: IIf(querystabilisatieinvoer.tblwatergehalte.monsternr="1" Or tblwatergehalte.monsternr="2" Or tblwatergehalte.monsternr="3";[avg13];IIf(querystabilisatieinvoer.tblwatergehalte.monsternr="4" Or tblwatergehalte.monsternr="5" Or tblwatergehalte.monsternr="6";[avg46];IIf(querystabilisatieinvoer.tblwatergehalte.monsternr="7" Or tblwatergehalte.monsternr="8" Or tblwatergehalte.monsternr="9";[avg79];"")))

 

[vbaInet]

Glad to hear of your progress