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!!!
Cobra967Asked:
Who is Participating?
 
Saurabh Singh TeotiaCommented:
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
 
Gustav BrockCIOCommented:
I think it should read:

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

/gustav
0
 
Saurabh Singh TeotiaCommented:
You can use this...

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

or

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

Saurabh...
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
PatHartmanCommented:
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
 
Cobra967Author 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
 
Gustav BrockCIOCommented:
The expression must be included in the Group By list.

/gustav
0
 
Cobra967Author 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
 
Saurabh Singh TeotiaCommented:
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;

Open in new window


Saurabh...
0
 
Cobra967Author 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
 
Gustav BrockCIOCommented:
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
 
Cobra967Author Commented:
LOL
0
 
Saurabh Singh TeotiaCommented:
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;

Open in new window


Saurabh...
0
 
Cobra967Author 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
 
Saurabh Singh TeotiaCommented:
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
 
Cobra967Author Commented:
Saurabh, it says that I have the wrong number of arguments.
0
 
Saurabh Singh TeotiaCommented:
My bad..Missed a bracket...

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
 
Gustav BrockCIOCommented:
I for sure need some more coffee ...

/gustav
0
 
Cobra967Author Commented:
The our users enter a 0 for length for any entry after the 50th record. I guess that will change the average?
0
 
Saurabh Singh TeotiaCommented:
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
 
Cobra967Author Commented:
Still error message: This expression is typed incorrectly, or it is too complex to be evaluated....
0
 
Saurabh Singh TeotiaCommented:
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
 
Cobra967Author Commented:
Same error
0
 
Saurabh Singh TeotiaCommented:
Can you post a copy of your database to see what you are doing..is your FishLenght number field im assuming..
0
 
Cobra967Author 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
 
Cobra967Author 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
 
Cobra967Author 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.

All Courses

From novice to tech pro — start learning today.