Comparing weeks with highest and lowest sales Query--HELP

[Neo]

Good Day,
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?

 

[Marshall Barton]

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)

 

[Neo]

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)?

 

[John Spencer]

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
'====================================================

 

[Marshall Barton]

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)?

I should not have tried to do it in a single query. Create
two queries:

qryHigh
SELECT TOP 3 DatePart("ww", saledate) As WeekNum,
Sum(QuantitySold) As WeekTotal
FROM Sales
GROUP BY DatePart("ww", saledate)
ORDER BY Sum(QuantitySold) DESC

and

qryLow
SELECT TOP 3 DatePart("ww", saledate) As WeekNum,
Sum(QuantitySold) As WeekTotal
FROM Sales
GROUP BY DatePart("ww", saledate)
ORDER BY Sum(QuantitySold)

Then the final query to combine those two results

SELECT WeekNum, WeekTotal FROM qryHigh
UNION ALL
SELECT WeekNum, WeekTotal FROM qryLow

Only the final query requires SQL view

 

[Neo]

Excellent! THANKS for the Help guys.
One more thing, I would like to show the actual dates of those weeks eg the
date of the Monday and Sunday alone, of that week,and the month of that week
so that the user would know the actual time frame.

 

[John Spencer]

You need to include that in your query somehow or calculate it. As far
as the MONTH goes, you have a problem since The month on Monday may be
one month earlier than the month on Sunday - weeks span months.

DateAdd("D",2-WeekDay(SaleDate),SaleDate) as Monday
DateAdd("D",7-Weekday(SaleDate),SaleDate) as Sunday

You can try something like the following:

SELECT TOP 3 DatePart("ww", Saledate) As WeekNum
, Sum(saleamount) As WeekTotal
, DateAdd("D",2-WeekDay(SaleDate),SaleDate) as Monday
, DateAdd("D",7-Weekday(SaleDate),SaleDate) as Sunday
FROM table
GROUP BY DatePart("ww", saledate)
, DateAdd("D",2-WeekDay(SaleDate),SaleDate)
, DateAdd("D",7-Weekday(SaleDate),SaleDate)
ORDER BY Sum(saleamount) DESC

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================