# Re: Top Values Query

Allen Browne
Guest
Posts: n/a

 14th Dec 2006
See:
How to Create a "Top N Values Per Group" Query
at:
http://support.microsoft.com/kb/210039

If the goal is a report, this might be easier:
How to Create a Top Values Per Group Report
at:
http://support.microsoft.com/kb/208822

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"K" <(E-Mail Removed)> wrote in message
news:BC9BEA3D-06BB-4E5D-B7CA-(E-Mail Removed)...
> Good day,
>
> My Query is set up like this. in Order from Top to botom = Left to Right.
> How do I get it to only display the Top 3 Downtimes in respect to the
> highest
> ammount of Duration for each CC_no (= Machine number) I have roughly 20
> machines.?
>
> Any help would be greatly appreciated.
>
> Field: CC_no
> Table: TopTenDT_P5
> Total: GroupBy
> Sort:
> Show Checked
> Criteria
> Or
>
> Field: Cost Center Description
> Table: TopTenDT_P5
> Total: GroupBy
> Sort:
> Show Checked
> Criteria
> Or
>
> Field: REASON
> Table: TopTenDT_P5
> Total: GroupBy
> Sort:
> Show Checked
> Criteria
> Or
>
> Field: Grp
> Table: TopTenDT_P5
> Total: GroupBy
> Sort:
> Show Checked
> Criteria
> Or
>
> Field: DT_DESC
> Table: TopTenDT_P5
> Total: GroupBy
> Sort:
> Show Checked
> Criteria
> Or
>
> Field: SumOfDT_DUR
> Table: TopTenDT_P5
> Total: Sum
> Sort:
> Show Checked
> Criteria
> Or

Allen Browne
Guest
Posts: n/a

 14th Dec 2006
Do you have a primary key in your table?

For this example, I assume the table is named Downtime, and has a primary
key field named DowntimeID:

SELECT DowntimeID, CC_No, DT_Dur
FROM Downtime
WHERE DowntimeID IN
(SELECT TOP 3 DowntimeID
FROM Downtime AS Dupe
WHERE Dupe.CC_No = Downtime.CC_No
ORDER BY Dupe.DT_Dur DESC, Dupe.DowntimeID DESC)
ORDER BY CC_No, DT_Dur DESC, DowntimeID DESC;

The query shows the top 3 downtime records for each machine.
The WHERE clause contains the subquery.
The subquery returns the primary key value of the TOP 3 downtimes for the
machine in the main query.
If the primary key value in the main query doesn't match one of those it
doesn't meet the WHERE clause.

For a basic introduction to subqueries, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"K" <(E-Mail Removed)> wrote in message
news:3A6F0A63-FC8D-4701-B40A-(E-Mail Removed)...
> Hi Allen,
>
> Thanks for helping me with this. The links are great. I am trying the
> first
> one.
>
> I have never wrote a subquery before. Can you possibly help me with this?
>
> I guess if I write what I am trying to accomplish in words it may help.
>
> Show me the top 3 values of Downtime (SumOfDT_DUR) for each Cost Center
> (CC_no)
>
> SQL = (Select Top 3 [SumOfDT_DUR] from TopTenDT_P5 Where_????
>
> I am playing around with the syntax but I am pretty sure I wont write the
> SQL properly. I fyou could help me I would appreciate it.
>
> Thanks
> Kyle.
>
>
>
>
> "Allen Browne" wrote:
>
>> See:
>> How to Create a "Top N Values Per Group" Query
>> at:
>> http://support.microsoft.com/kb/210039
>>
>> If the goal is a report, this might be easier:
>> How to Create a Top Values Per Group Report
>> at:
>> http://support.microsoft.com/kb/208822
>>
>> --
>> Allen Browne - Microsoft MVP. Perth, Western Australia
>> Tips for Access users - http://allenbrowne.com/tips.html
>> Reply to group, rather than allenbrowne at mvps dot org.
>>
>> "K" <(E-Mail Removed)> wrote in message
>> news:BC9BEA3D-06BB-4E5D-B7CA-(E-Mail Removed)...
>> > Good day,
>> >
>> > My Query is set up like this. in Order from Top to botom = Left to
>> > Right.
>> > How do I get it to only display the Top 3 Downtimes in respect to the
>> > highest
>> > ammount of Duration for each CC_no (= Machine number) I have roughly 20
>> > machines.?
>> >
>> > Any help would be greatly appreciated.
>> >
>> > Field: CC_no
>> > Table: TopTenDT_P5
>> > Total: GroupBy
>> > Sort:
>> > Show Checked
>> > Criteria
>> > Or
>> >
>> > Field: Cost Center Description
>> > Table: TopTenDT_P5
>> > Total: GroupBy
>> > Sort:
>> > Show Checked
>> > Criteria
>> > Or
>> >
>> > Field: REASON
>> > Table: TopTenDT_P5
>> > Total: GroupBy
>> > Sort:
>> > Show Checked
>> > Criteria
>> > Or
>> >
>> > Field: Grp
>> > Table: TopTenDT_P5
>> > Total: GroupBy
>> > Sort:
>> > Show Checked
>> > Criteria
>> > Or
>> >
>> > Field: DT_DESC
>> > Table: TopTenDT_P5
>> > Total: GroupBy
>> > Sort:
>> > Show Checked
>> > Criteria
>> > Or
>> >
>> > Field: SumOfDT_DUR
>> > Table: TopTenDT_P5
>> > Total: Sum
>> > Sort:
>> > Show Checked
>> > Criteria
>> > Or

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post donbexcel Microsoft Powerpoint 0 24th Mar 2007 06:31 PM =?Utf-8?B?UGF1bA==?= Microsoft Access Queries 5 31st Oct 2006 09:22 PM =?Utf-8?B?UmljayBWb295cw==?= Microsoft Access Queries 3 14th Oct 2005 08:19 PM =?Utf-8?B?VmFs?= Microsoft Access Queries 4 12th Sep 2005 11:01 AM Kenny Markhardt Microsoft Access Queries 1 1st Dec 2004 12:57 AM

Features