Help Me Fix Min Max Avg Querry

[Aamer]

I have made a querry which gives the following Results of Sale.

First Price
Last Price
Minimum Price
Maximum Price
Average Price

The querry is working perfectly, but the problem is sometimes we charge zero
value as the goods are either free to oblige certain clients or its sample.

I have to make an invoice of zero value to keep the inventory straight.

What i want is that it should skip the value Zero "0" and use the next value
which is greater than 0.

I tried with >0 in the criteria of querry under "First Price" and so on
with other, but the querry is not giving the correct result.


Can Someone Please Help Me Fix This Issue.


Thank You

Aamer

 

[Tom Wickerath]

Hi Aamer,

It would be helpful for you to include the SQL (Structured Query Language)
statement for your query when you post questions regarding queries. In query
design view, you should see a way of changing to SQL view (View | SQL View in
Access 2003 and prior versions).

It sounds to me like you are using the Totals function, and grouping by
First, Last, Minimum (Min), Maximum (Max) and Average (Avg). If this is what
you are doing, I'm not sure the First and Last groupings will return useful
information to you. The Access Help file includes the following:

First, Last Functions
"Return a field value from the first or last record in the result set
returned by a query."

and

"They simply return the value of a specified field in the first or last
record, respectively, of the result set returned by a query. Because records
are usually returned in no particular order (unless the query includes an
ORDER BY clause), the records returned by these functions will be arbitrary."

Try including Totals for the Min, Max and Avg only. Then add the same field
a fourth time, but change the Group By To Where, remove the check mark from
the Show box, and put a criteria of >0. Here is an example for the sample
Northwind database:

SELECT Min(UnitPrice) AS Minimum, Max(UnitPrice) AS Maximum,
Avg(UnitPrice) AS Average
FROM [Order Details]
WHERE UnitPrice>0;


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________