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
Similar Threads
