Consult with 10 bigger values

[Bob Quintal]

(e-mail address removed) wrote in

Dear all,

I'm trying to bring only the 10 bigger values in my consult:

This consult bring the customer name and the sales values...

SELECT Sales.name, Sum(Sales.tot) Expr1
FROM Model INNER JOIN Sales ON Model.model_orig = Sales.model
WHERE (((Left([invo_date],6))="200709"))
GROUP BY AZ_Sales.name, AZ_Sales.region
HAVING (((Sales.region)<>"EX"))
ORDER BY Sum(Sales.tot) DESC;

Then I click in properties in my consult structure and in
MaxRecords I put the number 10, but it doesn't work.

How can I do it work???

Thanks a lot!!!

André.

create a new query (consult) that uses the first.

 

[gatarossi]

Dear all,

I'm trying to bring only the 10 bigger values in my consult:

This consult bring the customer name and the sales values...

SELECT Sales.name, Sum(Sales.tot) Expr1
FROM Model INNER JOIN Sales ON Model.model_orig = Sales.model
WHERE (((Left([invo_date],6))="200709"))
GROUP BY AZ_Sales.name, AZ_Sales.region
HAVING (((Sales.region)<>"EX"))
ORDER BY Sum(Sales.tot) DESC;

Then I click in properties in my consult structure and in MaxRecords I
put the number 10, but it doesn't work.

How can I do it work???

Thanks a lot!!!

André.

 

[Pieter Wijnen]

SELECT Top 10 Sales.name, Sum(Sales.tot) as total
FROM Model INNER JOIN Sales ON Model.model_orig = Sales.model
WHERE Left([invo_date],6="200709"
and Sales.region<>"EX"
GROUP BY AZ_Sales.name, AZ_Sales.region
ORDER BY Sum(Sales.tot) DESC;

hth

Pieter

Dear all,

I'm trying to bring only the 10 bigger values in my consult:

This consult bring the customer name and the sales values...

SELECT Sales.name, Sum(Sales.tot) Expr1
FROM Model INNER JOIN Sales ON Model.model_orig = Sales.model
WHERE (((Left([invo_date],6))="200709"))
GROUP BY AZ_Sales.name, AZ_Sales.region
HAVING (((Sales.region)<>"EX"))
ORDER BY Sum(Sales.tot) DESC;

Then I click in properties in my consult structure and in MaxRecords I
put the number 10, but it doesn't work.

How can I do it work???

Thanks a lot!!!

André.