What Month did best Value Sale Occur

[Mr_Stanley]

Hello, I have a table which includes [Sales_Person] and [Sales_Value] and
[Sales_Date] ...I have used a select query to list Sales person, Total Value
of Sales, Count of Sales, and Max Value of Sale, is there a way I could list
what month their best sale occurred in? or am I going about it the wrong
way.

Regards Robert

 

[NetworkTrade]

it would seem your Sales Date field provides that....

perhaps I misunderstand.....

 

[John W. Vinson]

Hello, I have a table which includes [Sales_Person] and [Sales_Value] and
[Sales_Date] ...I have used a select query to list Sales person, Total Value
of Sales, Count of Sales, and Max Value of Sale, is there a way I could list
what month their best sale occurred in? or am I going about it the wrong
way.

Regards Robert

Where in this totals query is the sale date? You obviously can't determine
what month it's in if you've discarded the date. Do you want the month of the
best single sale, the best total for the month, or what?

 

[Mr_Stanley]

Many Thanks for your responses, I had a feeling I didn't explain it clearly,
I'm quite a novice at Access so I hope my question isn't silly, but
basically in the Totals I grouped on Sales_Person and used sum, count and
max against Sales_Value ......this gives me a single result for each sales
person.What I would also like to do in the same query is find the month of
the best single sale for each Sales Person and still only retrieve single
results. How do I use the Sales_date to achieve this? and not retrieve
multiple records.

Regards Robert

Hello, I have a table which includes [Sales_Person] and [Sales_Value] and
[Sales_Date] ...I have used a select query to list Sales person, Total
Value
of Sales, Count of Sales, and Max Value of Sale, is there a way I could
list
what month their best sale occurred in? or am I going about it the wrong
way.

Regards Robert

Where in this totals query is the sale date? You obviously can't determine
what month it's in if you've discarded the date. Do you want the month of
the
best single sale, the best total for the month, or what?

 

[John W. Vinson]

Many Thanks for your responses, I had a feeling I didn't explain it clearly,
I'm quite a novice at Access so I hope my question isn't silly, but
basically in the Totals I grouped on Sales_Person and used sum, count and
max against Sales_Value ......this gives me a single result for each sales
person.What I would also like to do in the same query is find the month of
the best single sale for each Sales Person and still only retrieve single
results. How do I use the Sales_date to achieve this? and not retrieve
multiple records.

Well, it sounds like you're mixing levels in this: you want to display the sum
across multiple records (across multiple dates), but ALSO display a date from
one single record (or possibly two or three if there are ties). This can be
done but gets a bit complicated! You will need a Subquery.

You say "you want to still only retrieve single records" and you ALSO say you
want to sum the records. You really can't have both, not in the same query!

Please post an example with four or five rows of data and the desired
appearance of the result.

 

[Mr_Stanley]

John,

Thank you for your perseverance with this, it is much appreciated. The
desired result
would be like this ( Month of Max Sale ) is the bit I can't get

Name Sum of all Sales Count of Sales Max Sale
Month of Max sale
Wiilms, Helen £150,000,48 678 £2,529
FEB
Wilson, Keith £541,755.23 1940 £11,628
APR

Many Thanks

Robert

 

[John W. Vinson]

John,

Thank you for your perseverance with this, it is much appreciated. The
desired result
would be like this ( Month of Max Sale ) is the bit I can't get

Name Sum of all Sales Count of Sales Max Sale
Month of Max sale
Wiilms, Helen £150,000,48 678 £2,529
FEB
Wilson, Keith £541,755.23 1940 £11,628
APR

I can't think of a way to BOTH get the sum and the month in the same query,
without using a somewhat clunky getaround, but the clumsy getaround should
work. In a vacant field cell put

Month Of Max Sale: Format(DLookUp("[Sale_Date]", "yourtablename",
"[Sales_Person] = """ & [Sales_Person] & """ AND [Sale] = " & MaxOfSales)

 

[Mr_Stanley]

John,

Just back into work this morning and have tried your solution, had a bit of
syntax error first time around but that was my fault, the end result though
works well. I would never have arrived at a soltion like, I'm so glad I
asked the experts.

Once again many many thanks

Robert

John,

Thank you for your perseverance with this, it is much appreciated. The
desired result
would be like this ( Month of Max Sale ) is the bit I can't get

Name Sum of all Sales Count of Sales Max Sale
Month of Max sale
Wiilms, Helen £150,000,48 678
£2,529
FEB
Wilson, Keith £541,755.23 1940
£11,628
APR

I can't think of a way to BOTH get the sum and the month in the same
query,
without using a somewhat clunky getaround, but the clumsy getaround should
work. In a vacant field cell put

Month Of Max Sale: Format(DLookUp("[Sale_Date]", "yourtablename",
"[Sales_Person] = """ & [Sales_Person] & """ AND [Sale] = " & MaxOfSales)