Problem with (select top 10...) placement 2

[tracy1234]

I have one table that shows customer_name, item, qty_billed.

I'm trying to return the top 10 items billed to each customer based on summing the qty_billed.

Here's what I have based on danvlas' May 13, 2003 thread where he gives this syntax:

"Select * From [My table] As A
Where Mytotal In
(Select Top 20 MyTotal
From [My table])
Where [Cust#] = A.[Cust#])"

SELECT
[2002 billing history].CUSTOMER_NAME,
[2002 billing history].ITEM,
Sum([2002 billing history].QUANTITY_INVOICED) AS sumOfQUANTITY_INVOICED

FROM [2002 billing history]

WHERE (SELECT TOP 10 Sum([2002 billing history].QUANTITY_INVOICED) from [2002 billing history])

GROUP BY [2002 billing history].CUSTOMER_NAME,
[2002 billing history].ITEM,

ORDER BY [2002 billing history].CUSTOMER_NAME,
[2002 billing history].ITEM,
Sum([2002 billing history].QUANTITY_INVOICED) DESC;

This does not limit the returned records to the top 10, it returns all of the items and their summed qty billed for each customer. How do I get only the top 10 per customer?

 

[scriverb]

Tracy: I worked on a thread with a similar need recently where we had to pick the Top 2 sales records by sales person. It seemed to work out okay and you could try doing something similar here.

thread700-545400

Let me know what you think of the proces.

Bob Scriver

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

 

[tracy1234]

Bob - I've read through your thread and and will pursue your method if I cannot debug my current sql. But, I'm really interested in why my method (based on danvlas)is not working.

-Tracy

 

[dodge20]

I think it is because you have the top 10 in the subquery, you need to put that into the top query, that way it will return only the top 10 records.


SELECT TOP 10,
[2002 billing history].CUSTOMER_NAME,
[2002 billing history].ITEM,
Sum([2002 billing history].QUANTITY_INVOICED) AS sumOfQUANTITY_INVOICED

FROM [2002 billing history]

WHERE (SELECT Sum([2002 billing history].QUANTITY_INVOICED) from [2002 billing history])

GROUP BY [2002 billing history].CUSTOMER_NAME,
[2002 billing history].ITEM,

ORDER BY [2002 billing history].CUSTOMER_NAME,
[2002 billing history].ITEM,
Sum([2002 billing history].QUANTITY_INVOICED) DESC;


Dodge20

 

[tracy1234]

Dodge20 -
That gives me the top 10 records, I need the top 10 per customer.

-Tracy

 

[scriverb]

Tracy: It is for that reason that I designed the method in the other thread I mentioned. The Top X will always select the top X of the whole recordset not by the Customer or whatever grouping you are after. I gried Top X with a group by and it still gave me the top ten of the whole recordset.

Bob Scriver

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

 

[CosmoKramer]

I would break this code into two seperate queries, with the first one called by the second one:

qryGetTotals

SELECT
 [2002 billing history].CUSTOMER_NAME,
 [2002 billing history].ITEM, 
 Sum([2002 billing history].QUANTITY_INVOICED) AS   sumOfQUANTITY_INVOICED

FROM [2002 billing history]

GROUP BY [2002 billing history].CUSTOMER_NAME, 
         [2002 billing history].ITEM,

ORDER BY [2002 billing history].CUSTOMER_NAME,
         [2002 billing history].ITEM,
         Sum([2002 billing history].QUANTITY_INVOICED) DESC;

qryTop10Totals

SELECT
 CUSTOMER_NAME,
 ITEM, 
 sumOfQUANTITY_INVOICED

FROM [qryGetTotals]
WHERE sumOfQUANTITY_INVOICED IN (SELECT TOP 10 sumOfQUANTITY_INVOICED) from [qryGetTotals] Order by sumOfQUANTITY_INVOICED DESC)
ORDER BY CUSTOMER_NAME,
 ITEM;

This is essentially pseudocode, I don't have Access in front of me now......

 

[tracy1234]

Cosmo - This gives the top 10 SumOfQUANTITY_INVOICED disregarding the per customer requirement.

Bob - simultaneously working on your recommendation.
I have my temp table and all my flags are set to 0. When I run the second query, it returns no records. Is this the expected results? Have not proceeded to the VB code yet.

query 1:

SELECT [2002 billing history].CUSTOMER_NAME,
[2002 billing history].ITEM,
Sum([2002 billing history].QUANTITY_INVOICED) AS sumOfQUANTITY_INVOICED,
False AS TOPFLAG INTO tblTEMPTOP
FROM [2002 billing history]
GROUP BY [2002 billing history].CUSTOMER_NAME,
[2002 billing history].ITEM
ORDER BY [2002 billing history].CUSTOMER_NAME,
[2002 billing history].ITEM,
Sum([2002 billing history].QUANTITY_INVOICED) DESC;

-->TOPFLAG is zero in my table

query 2:

SELECT *
FROM tblTEMPTOP AS TT
WHERE TT.TOPFLAG=True
ORDER BY TT.SumOfQUANTITY_INVOICED;

-->returns no records

-Tracy

 

[scriverb]

Yes, trace you must first run the first query to create the temporary table. Then execute the code which loops through the records and flags the top X records. Then you run the second query and select only those records wher the flag is set to true.

You will have to modify the slightly like two lines of code that look like this:
If vCounter < 3 Then

Change the 3 to a 11. This will flag only the top 10 in the table based on grouping.

Bob Scriver

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

 

[scriverb]

I have updated the code as I think it needs to be for your queries. I named them as you can see in the red. If you have a different just change the red code. I hate to sort and match based upon a name field as you are trying to do. Jim Smith records for two people will be combined. If you can add a Customer_ID field to uniquely identify the customers it would be better. You queries look good and if you run the following code the second query will open automatically with the top 10 by customer and Quantity Invoiced.
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim vSalesID As String
Dim vCounter As Integer
Set db = CurrentDb
DoCmd.SetWarnings False
DoCmd.OpenQuery &quot;qryMTTop10QtyInvd&quot;
DoCmd.SetWarnings True
Set rs = db.OpenRecordset(&quot;tblTempTop&quot;, dbOpenDynaset)
vSalesID = 0
vCounter = 1
rs.MoveFirst
vSalesID = rs(&quot;CUSTOMER_NAME&quot
Do
If vSalesID = rs(&quot;CUSTOMER_NAME&quot Then
If vCounter < 11 Then
rs.Edit
rs(&quot;TopFlag&quot = True
rs.Update
vCounter = vCounter + 1
End If
Else
vSalesID = rs(&quot;CUSTOMER_NAME&quot
vCounter = 1
If vCounter < 11 Then
rs.Edit
rs(&quot;TopFlag&quot = True
rs.Update
vCounter = vCounter + 1
End If
End If
rs.MoveNext
Loop Until rs.EOF
DoCmd.OpenQuery &quot;qryTop10QtyInvd&quot;

Let me know if this is successful.

Bob Scriver

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

 

[tracy1234]

Bob - I have made the changes to the VB and have added it to a command button. &quot;sort billing history bob&quot; is what I've been referring to as query 1, &quot;select top 10 from billing bob&quot; is query 2. Party_Site_Number is what I've simplified as Customer in previous posts.

I get a compile error. User-defined type not defined. I could be missing a reference. Do you know off-hand which one is required?

Private Sub Command5_Click()
On Error GoTo Err_Command5_Click

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;sort billing history bob&quot;
DoCmd.SetWarnings True
Set rs = db.OpenRecordset(&quot;tblTEMPTOP&quot;, dbOpenDynaset)
vCust_id = 0
vCounter = 1
rs.MoveFirst
vCust_id = rs(&quot;Party_Site_Number&quot
Do
If vParty_Site_Number = rs(&quot;Party_Site_Number&quot Then
If vCounter < 11 Then
rs.Edit
rs(&quot;TOPFLAG&quot = True
rs.Update
vCounter = vCounter + 1
End If
Else
vParty_Site_Number = rs(&quot;Party_Site_Number&quot
vCounter = 1
If vCounter < 11 Then
rs.Edit
rs(&quot;TOPFLAG&quot = True
rs.Update
vCounter = vCounter + 1
End If
End If
rs.MoveNext
Loop Until rs.EOF
DoCmd.OpenQuery &quot;select top 10 from billing bob&quot;

Exit_Command5_Click:
Exit Sub

Err_Command5_Click:
MsgBox Err.DESCRIPTION
Resume Exit_Command5_Click

End Sub

 

[tracy1234]

Bob - You're THE MAN!. I found Microsoft 3.6 DAO Object Library did the trick and it ran fine. Can't thank you enough for sticking with this.

-Tracy

 

[scriverb]

More than likely the MS Office DAO Object Library. If you are using 2000 or higher it would be 6.0.

Make sure you have updated your queries with the new field as an identifier.

Bob Scriver

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

 

[scriverb]

Great. Glad to be of assistance on this for you. I blew the version number in my last post. Sorry about that.

Thanks for the Star.

Bob Scriver

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

 

[icsupt]

Here's my statement. I'm trying to get the Top 10 Totalall from each Region.

Totalall is a calculation of several fields. So for each region, I want the top 10 amounts from Totalall. There are 4 regions, so I should have 40 amounts.

SELECT [Top 10].District, [Top 10].[Repetitivexx]*12+[Repetitive2]*12+[Repetitive3]*12+[Repetitive4]*12+[Repetitive5]*12+[One Timexx]+[One Time2]+[One Time3]+[One Time4]+[One Time5] AS Totalall,[Top 10].DateTransitioned, [Top 10].Term, [Top 10].[One Timexx], [Top 10].Repetitivexx, [Top 10].[One Time2], [Top 10].Repetitive2, [Top 10].[One Time3], [Top 10].Repetitive3, [Top 10].[One Time4], [Top 10].Repetitive4, [Top 10].[One Time5], [Top 10].Repetitive5, [Top 10].RIM
FROM [Top 10]
WHERE ((([Top 10].DateTransitioned) Is Null) AND (([Top 10].Term)=False))
ORDER BY [Top 10].Region;

 

[lespaul]

icsupt, you really should start your own thread instead of hijacking old ones.

You should list the tables and fields involved and how they are related to each other. Show some sample data from each of the tables. Then give an example of what you want the final results to look like.

For instance.

I have the following tables with data as shown:

tblPerson
PersonID (PK)          PersonName
   1                   Joe Blow
   2                   Jane Doe
   3                   Jack Black

tblSales
SaleID (PK)   PersonID (FK to tblPerson)   TotalSale
  1               1                           $15.00
  2               2                           $25.00
  3               1                           $15.00
  4               3                           $45.00
  5               2                           $30.00

I want to write a query that will give me:

PersonName        TotalAllSales
Joe Blow             $30.00
Jane Doe             $55.00
Jack Black           $45.00

Can someone assist in designing a query that will accomplish this?

Now, if you had presented your information in this way early this morning, you probably would have gotten a solution to your problem!!!

Leslie

 

[icsupt]

I thought by adding on to an existing post that was similar to what I wanted would make it easier.

I apologize. Certainly didn't want to hijack.

I will start a new thread or should I redo my question here? Now I'm confused.

 

[lespaul]

Start a new thread with the kind information I showed you above. Please be sure to show sample data and expected results.

Thanks,

Leslie