Append Query with Oldest top Records by Group

  • Thread starter Thread starter Aaron
  • Start date Start date
A

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
 
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
Click to expand...

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;
 
Back
Top