Max per Month 1

[WingandaPrayer]

Hi,

I'm trying to have the following in One query.

SalesName SalesFigure DateOfSale
Jones 77 1 April 2001
Smith 55 4 April 2001
Jones 87 7 April 2001
Davies 15 1 May 2001
Connell 25 2 May 2001
Wood 40 15 May 2001
Barber 78 30 May 2001
Jones 15 1 June 2001
Smith 20 7 June 2001
Jones 15 12 June 2001
Davies 64 20 June 2001
Connell 63 1 July 2001
Wood 80 15 July 2001
Barber 25 16 July 2001
Wood 15 25 July 2001

To Show this;

April 2001 87 Jones
May2001 78 Barber
June 2001 64 Davies
July 2001 80 Wood

With this
SELECT Format([DateOfSale],"mmmm yyyy&quot AS SaleMonth, Max(tblTheSales.SalesFigure) AS MaxOfSalesFigure, tblTheSales.SalesName
FROM tblTheSales
GROUP BY Format([DateOfSale],"mmmm yyyy&quot, tblTheSales.SalesName;

I still see all the rows.

If someone would give me a kick start !!

Thanks

David

 

[jtmach]

In order to make this work I had to build three queries. qryMaxDate1, qryMaxDate2 qryMaxDate3. The third one should give you the desired results. The SQL statements are below.

qryMaxDate1

SELECT Format([DateOfSale],"mmmm yyyy") AS SaleMonth, Max (SalesFigure) AS MaxOfSalesFigure FROM tblTheSales GROUP BY Format([DateOfSale],"mmmm yyyy");

qryMaxDate2

SELECT Format([DateOfSale],"mmmm yyyy") AS SaleMonth, SalesFigure, SalesName FROM tblTheSales;

qryMaxDate3

SELECT qryMaxDate.SaleMonth, qryMaxDate.MaxOfSalesFigure, qryMaxDate2.SalesName FROM qryMaxDate INNER JOIN qryMaxDate2 ON (qryMaxDate.SaleMonth = qryMaxDate2.SaleMonth) AND (qryMaxDate2.SalesFigure = qryMaxDate.MaxOfSalesFigure);

Hope this works for you.

The hardest questions always have the easiest answers.

 

[WingandaPrayer]

Thanks for your time and reply.
I thought it might be me not being able to create in one query.

David