Help with Top N query

  • Thread starter Thread starter Opal
  • Start date Start date
O

Opal

I am trying to create a query in Access 2003 which will show me the
Top
6 parts with downtime for the last 5 times the part has run... the
problem is
not all parts are "run" every day or every week, so I cannot give a
concrete date range for searching and use a between statement. One
part may have run 5 times in the last two weeks, but another may
have taken 2 months to run 5 times.

I have the following data in my table:

DTDate / PartNo / Line / Reason / DTMin

I want to be able to see the Top 6 DTMin by PartNo
going back for the past 5 runs for each part number
but I do not have a record of unique run numbers so
I just need to go back as far as I can by date in order
to get 5 runs for each of my top 6 parts as well as show
the reason for the downtime. Can anyone help
point me in the right direction to achieve this?
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

What is DTMin?  Is it minutes, minumum?

What do you mean by "top 6 parts"?  What criteria defines a part as in
the top 6?

Probably something like this (not sure, until I know what DTMin is):

SELECT TOP 6 DTMin, PartNo, Reason
FROM table_name As T1
WHERE PartNo In (SELECT PartNo FROM table_name
                  GROUP BY PartNo, DTDate
                  HAVING COUNT(*) = 5
                  ORDER BY DTDate DESC)
ORDER BY PartNo, DTMin DESC

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup.  I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSbMQnoechKqOuFEgEQKGsQCffh4dzkycNaDtRMoRwyg5Y8/Q/14AoOnR
9Z9mbxrhm4mxZ6H0nXSjH6iX
=1gbu
-----END PGP SIGNATURE------ Hide quoted text -

- Show quoted text -
Click to expand...

Sorry, DTMIN is the downtime in minutes for the part that was run.
A Top 6 part would be one of 6 parts with the highest downtime
in the last 5 runs of that part.
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

What is DTMin?  Is it minutes, minumum?

What do you mean by "top 6 parts"?  What criteria defines a part as in
the top 6?

Probably something like this (not sure, until I know what DTMin is):

SELECT TOP 6 DTMin, PartNo, Reason
FROM table_name As T1
WHERE PartNo In (SELECT PartNo FROM table_name
                  GROUP BY PartNo, DTDate
                  HAVING COUNT(*) = 5
                  ORDER BY DTDate DESC)
ORDER BY PartNo, DTMin DESC

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup.  I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSbMQnoechKqOuFEgEQKGsQCffh4dzkycNaDtRMoRwyg5Y8/Q/14AoOnR
9Z9mbxrhm4mxZ6H0nXSjH6iX
=1gbu
-----END PGP SIGNATURE------ Hide quoted text -

- Show quoted text -
Click to expand...

Thank you for your assistance, unfortuantely, that query gives me the
same
part number for each specific line. I need to be able to run the
query for
each of the 9 production Lines.
 
Thank you for your assistance, unfortuantely, that query gives me the
same
part number for each specific line.  I need to be able to run the
query for
each of the 9 production Lines.- Hide quoted text -

- Show quoted text -
Click to expand...

I have tried the following subquery, but don't have it quite rightL

SELECT DTMAST.DTDATE, DTMAST.LINE, DTMAST.PARTNO, DTMAST.REASON,
DTMAST.DTMIN
FROM DTMAST
WHERE DTMAST.DTMIN IN
(SELECT TOP 6 DTMAST.PARTNO
FROM DTMAST
WHERE (((DTMAST.LINE)="ProdLine1"))
ORDER BY DTMAST.DTMIN DESC,DTMAST.LINE, DTMAST.DTDATE);

as I am getting a type mismatch error...
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Thanks for the info.  Let me re-state your criteria so you know I'm
thinking the same thing you are.  You want the part numbers that had the
top 6 down times (in minutes) for each part's last 5 runs.  Does that
mean you want only 6 part numbers?  Because the top 6 down times can
only be associated with 6 part numbers (barring ties in down time).

Maybe something like this:

SELECT TOP 6 DTDate, PartNo, Reason, DTMin
FROM DTMast AS M1
WHERE Line = 'ProdLine1'
AND DTDate In (SELECT DISTINCT TOP 5 DTDate
                FROM DTMast
                WHERE PartNo = M1.PartNo
                AND Line = 'ProdLine1'
                ORDER BY DTDate DESC
                )
GROUP BY PartNo
ORDER BY DTMin DESC

If this doesn't work please show me an example of the data in DTMast and
how you expect the result of the query to look.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup.  I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSbSM34echKqOuFEgEQKn3QCgz8+F6EROlV7Mc/G+2ISwD6TqJcsAn1iZ
eNQ0wY7i3k7O/DiHj3Ep7IPF
=LbI4
-----END PGP SIGNATURE------ Hide quoted text -

- Show quoted text -
Click to expand...

Hi MG

Thank you for your reply..... I get an error telling met that
the express DTDate is not part of an aggregate function...?
 
Hi MG

Thank you for your reply.....  I get an error telling met that
the express DTDate is not part of an aggregate function...?- Hide quoted text -

- Show quoted text -
Click to expand...

I think I want to tackle this from a different angle....How would I
show the last
5 instances (or production runs) for each and every part number?
 
Back
Top