Group/Top 10 queries won't work (1 Viewer)

[Neal]

I am trying to build a query that returns the top ten costs for separate billing cycles. I have tried two approaches: one discussed on this forum and in the MS Knowledge base, and another I got from another Access site. I can't get either to work. Does anyone know why:

Approach 1:
SELECT a.TotCost, a.BillingDate, a.PhoneID
FROM tblSprint AS a
WHERE (((a.TotCost) In (Select Top 10 TotCost from [tblSprint] where [BillingDate]=a.[BillingDate] and [PhoneID]=a.[PhoneID] Order By [BillingDate] desc)));

Approach 2:
FROM qrySprintMonthly AS a
WHERE (((a.BillingDate)>#1/1/2001#) AND (((Select Count(*) from qrySprintMonthly where BillingDate = a.BillingDate and PhoneId = a.PhoneID and AssignedTo = a.AssignedTo)) Between 1 And 10))
ORDER BY a.BillingDate DESC;

They both return all the records.
Thanks,
Neal

 

[dcx693]

What about just this:

Select Top 10 TotCost,BillingDate,PhoneID from tblSprint Order By BillingDate desc;

 

[Neal]

No, that won't do it

What about just this:

Select Top 10 TotCost,BillingDate,PhoneID from tblSprint Order By BillingDate desc;

That returns the top 10 for all of the billing dates. I need the top ten for Jan., Feb., March, etc.

 

[dcx693]

Have you tried the technique in this article? How to Create a Top Values Per Group Report

 

[Neal]

Group/Top Values

Have you tried the technique in this article? How to Create a Top Values Per Group Report

I solved my problem by removing the PhoneID field. Although it is a text field, it is populated only by numbers, which were throwing off the Top N process. I will take a look at the sample reports database mentioned on the Microsoft page. I expect I will use it down the road.

Thanks,
Neal