Append Query with Oldest top Records by Group

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

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/For...eries/201004/1

On Mon, 19 Apr 2010 17:13:01 -0700, Aaron
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]