# avg and roud only some record using ms access query expression

Hi folks, I need your help in figure out what I am doing wrong on with the expression below. In short, I need to do the average and rounding that average to 2 decimal places fish that are greater than 2 in length. It is imperative that it is done as "expression" since the same query has other calculation that counts the same records differently.

Non working expression: IIf([FishLenght]>2,Round(Avg([FishLenght]),2))

Working expression without filtering specific fish length: Round(Avg([FishLenght]),2)

Thank you!!!
###### Who is Participating?

Commented:
Basis of the logic can't we do sum()/Count()

or alternatively like gustav suggested create a sub query of your data do a simple average on it and then use it here to get the necessary value you looking for... as for me even 0 is not greater then 2 then not sure why its including in the average...
0

CIOCommented:

IIf([FishLenght]>2,Round(Avg([FishLenght]),2),[FishLenght])

/gustav
0

Commented:
You can use this...

IIf([FishLenght]>2,round(Avg([FishLenght]),2),0)

or

round(IIf([FishLenght]>2,Avg([FishLenght]),0),2)

Saurabh...
0

Commented:
If you want to average only the fish greater than 2 in in length then those are the ONLY fish that should be selected.  OR you need to add a grouping so that you do the average for fish within groups.  The aggregation done in a query is over the entire domain.  The domain is the entire recordset or if you include grouping, then over the group.

Select IIf(FishLength < 2, "< 2", ">= 2") as LengthGroup, Avg(FishLength)
From YourTable
Group by IIf(FishLength < 2, "< 2", ">= 2") ;
0

Author Commented:
No matter which of the above expression I am using, it get the following error message: Your query does not include the specific expression  'IIf([FishLenght]>2,round(Avg([FishLenght]),2),0)' as part of an aggredate function.

My full statement is:

SELECT FISHSAMPLESUMMARY.SummaryID, FISHSAMPLESUMMARY.Fish, FISHSAMPLESUMMARY.TotDELT AS DELT, FISHSAMPLESUMMARY.MultipleAge AS MA, IIf([FishLenght]>2,Round(Avg([FishLenght]),2),0) AS [Avg Lenght], Sum(FISHSAMPLEDETAILS.Quantity) AS Total
FROM FISHSAMPLESUMMARY LEFT JOIN FISHSAMPLEDETAILS ON FISHSAMPLESUMMARY.SummaryID = FISHSAMPLEDETAILS.SummaryID
WHERE (((FISHSAMPLESUMMARY.SampleID)=[Forms]![Fish Sample]![SampleID]))
GROUP BY FISHSAMPLESUMMARY.SummaryID, FISHSAMPLESUMMARY.Fish, FISHSAMPLESUMMARY.TotDELT, FISHSAMPLESUMMARY.MultipleAge
ORDER BY FISHSAMPLESUMMARY.SummaryID DESC;

Any idea? Also, this expression
0

CIOCommented:
The expression must be included in the Group By list.

/gustav
0

Author Commented:
If I change from Expression to Group By then I get: Cannot have aggregate function in GROUP BY clause IIf([FishLenght]>2,round(Avg([FishLenght]),2),0)).
0

Commented:
Use this...

``````SELECT FISHSAMPLESUMMARY.SummaryID, FISHSAMPLESUMMARY.Fish,
FISHSAMPLESUMMARY.TotDELT AS DELT, FISHSAMPLESUMMARY.MultipleAge AS MA,
round(IIf([FishLenght]>2,Avg([FishLenght]),0),2) AS [Avg Lenght], Sum(FISHSAMPLEDETAILS.Quantity) AS Total
FROM FISHSAMPLESUMMARY LEFT JOIN FISHSAMPLEDETAILS ON FISHSAMPLESUMMARY.SummaryID = FISHSAMPLEDETAILS.SummaryID
WHERE (((FISHSAMPLESUMMARY.SampleID)=[Forms]![Fish Sample]![SampleID]))
GROUP BY FISHSAMPLESUMMARY.SummaryID, FISHSAMPLESUMMARY.Fish,
FISHSAMPLESUMMARY.TotDELT, FISHSAMPLESUMMARY.MultipleAge
ORDER BY FISHSAMPLESUMMARY.SummaryID DESC;
``````

Saurabh...
0

Author Commented:
I am still getting error message: Your query does not include the specific expression  'Round(IIf([FishLenght]>2,Avg([FishLenght]),0),2)' as part of an aggredate function.

:-(
0

CIOCommented:
Yes, you are in trouble. You may need to have subquery to precalculate the Average, or use an expression like that you have with DAvg(...)

/gustav
0

Author Commented:
LOL
0

Commented:
I believe you don't want to group on that you have used avg at wrong place..use this way...

``````SELECT FISHSAMPLESUMMARY.SummaryID, FISHSAMPLESUMMARY.Fish,
FISHSAMPLESUMMARY.TotDELT AS DELT, FISHSAMPLESUMMARY.MultipleAge AS MA,
round(avg(IIf([FishLenght]>2,[FishLenght],""),2) AS [Avg Lenght], Sum(FISHSAMPLEDETAILS.Quantity) AS Total
FROM FISHSAMPLESUMMARY LEFT JOIN FISHSAMPLEDETAILS ON FISHSAMPLESUMMARY.SummaryID = FISHSAMPLEDETAILS.SummaryID
WHERE (((FISHSAMPLESUMMARY.SampleID)=[Forms]![Fish Sample]![SampleID]))
GROUP BY FISHSAMPLESUMMARY.SummaryID, FISHSAMPLESUMMARY.Fish,
FISHSAMPLESUMMARY.TotDELT, FISHSAMPLESUMMARY.MultipleAge
ORDER BY FISHSAMPLESUMMARY.SummaryID DESC;
``````

Saurabh...
0

Author Commented:
I have a specific fish sample that include a certain number of fish. However, I only have length for the first 50 fish or so. After that the user enter a 0 (I used 2 as example) as length and enter the number of fish left  (e.g. 75). Therefore the total fish collected will be 125. However, the average needs to be computed only considering the fish that have a length > 0 (or 2 in my example) The first 50 records will represent  1 single fish with its lengths. Record 51 will include the sum of what is left on hand. Confused yet?
0

Commented:
Yes it will calculate the average of the remain records only..since the once which don't meet your crteria will be "" and if you notice if you include blank in the average it doesn't change the average..you can compare the same with the answer you get...

Saurabh...
0

Author Commented:
Saurabh, it says that I have the wrong number of arguments.
0

Commented:

SELECT FISHSAMPLESUMMARY.SummaryID, FISHSAMPLESUMMARY.Fish,
FISHSAMPLESUMMARY.TotDELT AS DELT, FISHSAMPLESUMMARY.MultipleAge AS MA,
round(avg(IIf([FishLenght]>2,[FishLenght],"")),2) AS [Avg Lenght], Sum(FISHSAMPLEDETAILS.Quantity) AS Total
FROM FISHSAMPLESUMMARY LEFT JOIN FISHSAMPLEDETAILS ON FISHSAMPLESUMMARY.SummaryID = FISHSAMPLEDETAILS.SummaryID
WHERE (((FISHSAMPLESUMMARY.SampleID)=[Forms]![Fish Sample]![SampleID]))
GROUP BY FISHSAMPLESUMMARY.SummaryID, FISHSAMPLESUMMARY.Fish,
FISHSAMPLESUMMARY.TotDELT, FISHSAMPLESUMMARY.MultipleAge
ORDER BY FISHSAMPLESUMMARY.SummaryID DESC;

Saurabh...
0

CIOCommented:
I for sure need some more coffee ...

/gustav
0

Author Commented:
The our users enter a 0 for length for any entry after the 50th record. I guess that will change the average?
0

Commented:
How you are capturing the user input...? Because right now its giving you a hardcorded in the query 2..either you can map this as well to the user input..
0

Author Commented:
Still error message: This expression is typed incorrectly, or it is too complex to be evaluated....
0

Commented:
It should not..Help me understand..whether this query gives you an answer...

SELECT FISHSAMPLESUMMARY.SummaryID, FISHSAMPLESUMMARY.Fish,
FISHSAMPLESUMMARY.TotDELT AS DELT, FISHSAMPLESUMMARY.MultipleAge AS MA,
avg(IIf([FishLenght]>2,[FishLenght],""))AS [Avg Lenght], Sum(FISHSAMPLEDETAILS.Quantity) AS Total
FROM FISHSAMPLESUMMARY LEFT JOIN FISHSAMPLEDETAILS ON FISHSAMPLESUMMARY.SummaryID = FISHSAMPLEDETAILS.SummaryID
WHERE (((FISHSAMPLESUMMARY.SampleID)=[Forms]![Fish Sample]![SampleID]))
GROUP BY FISHSAMPLESUMMARY.SummaryID, FISHSAMPLESUMMARY.Fish,
FISHSAMPLESUMMARY.TotDELT, FISHSAMPLESUMMARY.MultipleAge
ORDER BY FISHSAMPLESUMMARY.SummaryID DESC;

or it gives you are error?
0

Author Commented:
Same error
0

Commented:
Can you post a copy of your database to see what you are doing..is your FishLenght number field im assuming..
0

Author Commented:
The DB is very large, but I can post a screenshot of the form showing where the query is used to computer the total and the entry form. In the attached example, the average is wrong because it divide 2 by 3, instead it should divide 2 by 2 because for average purpose the record with 0 length does not count.
Fish.jpg
0

Author Commented:
I guess I can force the length to be  null when the Quantity is > 1 and the average is computed correctly. When entering a 0 that will mess up the average. I did not expected to be so complicated to average only on certain numbers.
0

Author Commented:
At this point I think it is easier to not allow 0 length for quantity > 1. Thank you for your effort anyway. I will share the points.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.