Prev: creating a flag to identify first, second time etc someone hasapp
Next: field no show
From: Aaron on 19 Apr 2010 20:13
Hello All,

I am not sure where even to began to start on this query.

I am looking for a query that on table tblmaxdata that has the following
fields (PN, QTY, CYCDATE, ABCCLASS) The ABCCLASS has values of A, B, C. I
am looking to get 20 oldest date (CYCDATE) records that have the value of "A"
in ABCCLASS, plus the top 10 Oldest date (CYCDATE) records that have "B" in
ABCCLASS, plus the top 10 Oldest date (CYCDATE) records that have "C" in
ABCCLASS.

Is this possible and if so how would I even begin to create this?

Thanks in advance for any help on this headache query. lol

Thanks,
Aaron
From: PieterLinden via AccessMonster.com on 19 Apr 2010 20:44
Three top values queries unioned together should do it. Something like...

SELECT TOP 20 PN, Qty, CYCDate, ABCClass
FROM tblMaxData
WHERE ABCClass = 'A'
ORDER BY CYCDate ASC
UNION
SELECT TOP 10 PN, Qty, CYCDate, ABCClass
FROM tblMaxData
WHERE ABCClass = 'B'
ORDER BY CYCDate ASC
UNION
SELECT TOP 10 PN, Qty, CYCDate, ABCClass
FROM tblMaxData
WHERE ABCClass = 'B'
ORDER BY CYCDate ASC;

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201004/1

From: John W. Vinson on 19 Apr 2010 20:46
On Mon, 19 Apr 2010 17:13:01 -0700, Aaron <Aaron(a)discussions.microsoft.com>
wrote:

>Hello All,
>
>I am not sure where even to began to start on this query.
>
>I am looking for a query that on table tblmaxdata that has the following
>fields (PN, QTY, CYCDATE, ABCCLASS) The ABCCLASS has values of A, B, C. I
>am looking to get 20 oldest date (CYCDATE) records that have the value of "A"
>in ABCCLASS, plus the top 10 Oldest date (CYCDATE) records that have "B" in
>ABCCLASS, plus the top 10 Oldest date (CYCDATE) records that have "C" in
>ABCCLASS.
>
>Is this possible and if so how would I even begin to create this?
>
>Thanks in advance for any help on this headache query. lol
>
>Thanks,
>Aaron

Do you in fact want an *append* query to store this data redundantly in a
second table?

I'd use a UNION query instead to dynamically get the oldest records as of the
time you run the query:

SELECT TOP 20 PN, QTY, CYCDATE, ABCCLASS
FROM tblMaxdate
WHERE ABCCLASS = "A"
ORDER BY CYCDATE DESC
UNION ALL
SELECT TOP 10 PN, QTY, CYCDATE, ABCCLASS
FROM tblMaxdate
WHERE ABCCLASS = "B"
ORDER BY CYCDATE DESC
UNION ALL
SELECT TOP 10 PN, QTY, CYCDATE, ABCCLASS
FROM tblMaxdate
WHERE ABCCLASS = "C"
ORDER BY CYCDATE DESC;

--

John W. Vinson [MVP]
 | 
Pages: 1
Prev: creating a flag to identify first, second time etc someone hasapp
Next: field no show