Re: Comparing weeks with highest and lowest sales Query--HELP

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

I think you will have to use nested queries to get the result you want.

Three queries.
First query saved as QueryOne:
SELECT TOP 3 DatePart("ww", saledate) As WeekNum
, Sum(saleamount) As WeekTotal
FROM table
GROUP BY DatePart("ww", saledate)
ORDER BY Sum(saleamount) DESC

Second query saved as QueryTwo:
SELECT TOP 3 DatePart("ww", saledate) As WeekNum
, Sum(saleamount) As WeekTotal
FROM table
GROUP BY DatePart("ww", saledate)
ORDER BY Sum(saleamount) ASC

Third query using UNION to combine the results of the first two queries:
SELECT * FROM QueryOne
UNION
SELECT * FROM QueryTwo

The problem is that UNION queries ignore any order by clauses in the component queries. You can have only one ORDER By clause at the END of the Union query and that is based on the names of the field in the first component query
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


Neo wrote:
When I entered the code into a blank query I got the following error message:

The ORDER BY expression (Sum(QuantitySold)) includes fields that are not selected by the query. Only those fields requested in the first query can be included in an ORDER BY expression.

P.S.- QuantitySold is the name of my field in my database. and also, are the two tables in the FROM statements suppose to be the same table (sales)?


"Marshall Barton" wrote:

Neo wrote:
i would like to create a query to retrieve the total number of sales for the week and output the 3 highest and lowest weeks for the year (in other words finding the weeks with the most and least number of sales).
Could someone inform me how to go about creating this query, specifically the formulars to use or if i need to write SQL for it?

This way requires SQL view:

SELECT TOP 3 DatePart("ww", saledate) As WeekNum,
Sum(saleamount) As WeekTotal
FROM table
GROUP BY DatePart("ww", saledate)
ORDER BY Sum(saleamount) DESC
UNION ALL
SELECT TOP 3 DatePart("ww", saledate) As WeekNum,
Sum(saleamount) As WeekTotal
FROM table
GROUP BY DatePart("ww", saledate)
ORDER BY Sum(saleamount)

--
Marsh
MVP [MS Access]

.

Relevant Pages

  • Re: Weird speed problem
    ... to design a system that queries this table to produce fast counts. ... I would assume that the first query should run faster then the second ... When I look at the query plans, they are almost identical cost ...
    (comp.databases.ms-sqlserver)
  • Re: Simply confused - Query stopped working
    ... Here is my sql for the 2 queries (and yes I know there are ... Is Not Null) part of the query. ... Another approach would be to (first, make a backup!) use Compact & Repair to ... first Query and since I can use it in a iifexpression? ...
    (microsoft.public.access.queries)
  • Weird speed problem
    ... to design a system that queries this table to produce fast counts. ... I would assume that the first query should run faster then the second ... When I look at the query plans, they are almost identical cost ...
    (comp.databases.ms-sqlserver)
  • Re: Need advice on speeding query up
    ... Any field your queries use in ORDER BY, GROUP BY or WHERE clauses, and any ... into the next (a Crosstab query), and then the second query is fed to a ... The first query is called qryCustomerAggregate, ...
    (microsoft.public.access.queries)
  • RE: Dynamically referencing a recordset?
    ... through that recordset to set the query parameters in VBA. ... to be the one who had to modify 1440 queries if there is a change in the ... different table schema, then you need 3, one for each record type. ... This is a database that tracks the production on records in another ...
    (microsoft.public.access.modulesdaovba)