Finding Best 2 of x 1

[MTBChik]

I am trying to write a query that will take the best two sales for a single salesperson, then average them. Some salespeople have one or two sales, and some have several hundred.

My table is set up thusly:
SalesID, SalesAmount, SalesType

SalesType doesn't matter. I want to take all types in figuring out their average.

I'm guessing I'll have to write this in sql, but can't quite get access to cooperate.

 

[scriverb]

This requires two queries and a little VBA code.
Create a query and name it qryMTTop2Sales. Copy and paste the following SQL into the SQL window. Enter your sales data table name in place of the red code:
SELECT A.SalesID, A.SalesAmount, False AS Top2Flag INTO tblTempTop2Sales
FROM <tblSalesData> AS A
ORDER BY A.SalesID, A.SalesAmount DESC;
This will create a temporary table called tblTempTop2Sales

Create another query and name it qryTop2SalesData. Copy and paste the following SQL into the SQL window.
SELECT A.SalesID, Sum(A.SalesAmount) AS Top2Sales, Top2Sales/2 AS Top2Sales_Average
FROM tblTempTop2Sales AS A
WHERE (((A.Top2Flag)=True))
GROUP BY A.SalesID
ORDER BY A.SalesID;

Copy the following VBA code and paste it behind a command button to initiate the process:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim vSalesID As Long
Dim vCounter As Integer
Set db = CurrentDb
DoCmd.SetWarnings False
DoCmd.OpenQuery &quot;qryMTTop2Sales&quot;
DoCmd.SetWarnings True
Set rs = db.OpenRecordset(&quot;tblTempTop2Sales&quot;, dbOpenDynaset)
vSalesID = 0
vCounter = 1
rs.MoveFirst
vSalesID = rs(&quot;SalesID&quot
Do
If vSalesID = rs(&quot;SalesID&quot Then
If vCounter < 3 Then
rs.Edit
rs(&quot;Top2Flag&quot = True
rs.Update
vCounter = vCounter + 1
End If
Else
vSalesID = rs(&quot;SalesID&quot
vCounter = 1
If vCounter < 3 Then
rs.Edit
rs(&quot;Top2Flag&quot = True
rs.Update
vCounter = vCounter + 1
End If
End If
rs.MoveNext
Loop Until rs.EOF
DoCmd.OpenQuery &quot;qryTop2SalesData&quot;

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[Hap007]

Hey Bob,

Pretty neat, change color for your code example.

Can you tell me how you did that in this forum?

Thanks,
Hap



Access Developer

http://www.fcs-software.com/fcsaccessdev.html

-
Specializing in Access based Add-on Solutions for the Developer

 

[scriverb]

There is a link at the bottom where you type your replies. Called Process TGML. Follow the link. Displays coding for colors, bolds, etc. Also, Emoticons/Smileys on another link.

Yea, I have been doing this because it is easier to make sure the code I provide is interrpreted correctly. Points out parts of code that have to be updated with info specific.

Have fun and be creative.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[MTBChik]

Thank you! I figured there'd be a bit of coding involved, but I'd been looking at the darn thing too long and couldn't see the forest for the trees!

MTB