Append Query with Oldest top Records by Group

[Aaron]

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

 

[PieterLinden via AccessMonster.com]

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;

 

[John W. Vinson]

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;