Query to select greatest sale amount per day in every month

Here's one way to do that:

SELECT Max(tblYourTable.[Sales_Field]) AS MaxOfSales_Field, Format([Date_Field],"mmmm") AS Sales_Month
FROM tblYourTable
GROUP BY Format([Date_Field],"mmmm"), Format([Date_Field],"m")
ORDER BY Format([Date_Field],"m");

Select allOpen in new window

The Date_Seq field will allow you to list your data in numerical order as opposed to alphabetical.

Paul

Thanks
it works fine, you gave me the greatest sale amount of one sale.
but on one day there could be multiple sales and i need the total amount for that day.
we need to sum by day first.
thanks

Sorry, missed that part. These two queries should get you the desired results. This query will get the daily totals:

SELECT tblYourTable.[DateField], Sum(tblYourTable.[SalesField]) AS SumOfSalesField
FROM tblYourTable
GROUP BY tblYourTable.[DateField];

Select allOpen in new window

Based off of that query, this one will get you the monthly max totals:

SELECT Format([DateField],"mmmm") AS Sales_Month, Max(qrySalesByDay.SumOfSalesField) AS MaxOfSumOfSalesField
FROM qrySalesByDay
GROUP BY Format([DateField],"mmmm"), Format([DateField],"m")
ORDER BY Format([DateField],"m");

Select allOpen in new window

If your data covers multiple years you can modify the 4th line of the second query to this:

GROUP BY Format([DateField],"mmmm yyyy"), Format([DateField],"yyyymm")

Select allOpen in new window

Consider using column =max(column)

Look at row_number order by sale price and display just the first row.

Aikimark,

i like your code, i tweaked it a little and it works very good.
i added to get the total Qty of the day of the selected Max SaleAmt, but it dosent work. Can you see why?

select month(salesdate), max(sumsales) as maxsalestotalinmonth,  max(sumQty) as maxQtytotalinmonth
from   (select salesdate, sum(sales) as sumsales, sum(Qty) as sumQty    from yoursalestable    group by salesdate   ) group by month(salesdate)

Thanks

Are you only interested in the highest sale on one day, or a list of highest sales during the past seven days as an example.

you have to view the dataset such that you can pull one row out.


Can you post a sample data set

have you had a chance to look at row_number?
Use it to partition the data into daily sales, with the counter starting at 1 when the price is highest on that day.,

you can then select rows with the counter entry column  =1.

Can you see why?

This is a limitation that we frequently encounter.  It is an old and recurring problem.

I'd like to do some refactoring of the SQL with this next iteration.

Create a new query, named sumdailysales:

PARAMETERS [EnterStartDate] DateTime, 
                          [EnterEndDate] DateTime; 
Select salesdate, sum(sales) as sumsales, 
               sum(Qty) as sumQty
From yoursalestable
Where salesdate Between [EnterStartDate] And [EnterEndDate]
Group By salesdate

Select allOpen in new window

Note: This query will now prompt you for a date range.

Next, we change the outer query to use this new query; a fairly straightforward substitution.

Select month(salesdate) as maxSDmonth, 
       max(sumsales) as maxsalestotalinmonth,  
       max(sumQty) as maxQtytotalinmonth

From   sumdailysales

Group by month(salesdate)

Select allOpen in new window

Aside from the date prompts and a column name, you should see the same output.  Don't worry.  We're not finished.

Assuming that you want to know the actual date of maxsalestotalinmonth for each month in the range, we need to wrap this query, like we did in the first iteration.

Select maxSDmonth, maxsalestotalinmonth, maxQtytotalinmonth, 
    sumdailysales.salesdate

From
(Select month(salesdate) as maxSDmonth, 
       max(sumsales) as maxsalestotalinmonth,  
       max(sumQty) as maxQtytotalinmonth

From   sumdailysales

Group by month(salesdate)
) As maxsalesdateinmonth
Inner Join sumdailysales On Month(sumdailysales.salesdate) = maxsalesdateinmonth.maxSDMonth And 
sumdailysales.sumsales = maxsalesdateinmonth.maxsalestotalinmonth

Select allOpen in new window

Oops, did not notice that the question deals with Access. my suggested options might not be available in this context.

it's working fine with only the sale amount.
(for now it will be enough with out the Qty)