How create query to capture last three records for a key number

Use
Select Top 3 ....
From yourtable
Order by ID desc;

Sort the file descending so the last three are at the top and then use the Top 3 predicate.

Try using a Where clause:
Where id In (Select Top 3 OpenOrderID From theQueryThatIncludes_OpenOrderID Order By theDate Desc)

Not working.  I'll try to explain again,

I have a table named tblOpenOrders.  I also have a table named tblFinished.  They are linked by a field named "PartN".

For the last 3 records in tblOpenOrders for each PartN I want to sum the total of the values in a field named "ScrapQty" in tblFinished.

so it will be clear, upload a sample db with the two tables...

Attached.

Again, for every record in tblOpenOrders, I want to sum the ScrapQty (from tblFinished) from the three last orders.  If the answer is 0 then I need it to say 0.

I sure hope I'm explaining this well.

Steve
Query-Problem.zip

test this query


SELECT TOP 3 tblOpenOrders.RecID, tblOpenOrders.Part_No, F.SumOfQtyScrap
FROM tblOpenOrders INNER JOIN
(
SELECT tblFinished.PartN, Sum(tblFinished.QtyScrap) AS SumOfQtyScrap
FROM tblFinished
GROUP BY tblFinished.PartN
) As F
ON tblOpenOrders.Part_No=F.PartN
ORDER BY tblOpenOrders.RecID DESC


the last three orders were based on the last three RecID..

if this is not what you want, Explain..

Not quite right.  The result of the query should be a record for every RecID in tblOpenOrders.

So it should list the ordID and then in column 2, the sum of the scrap from the last 3 orders in tblOpenOrders for that OrdID.

Make sense?

open the table  tblOpenOrders, copy the last 3 orders and post here and explain why they are the last 3 orders.

To stop me from assuming, Resend the database with just few records. 10-15 records will be fine. You don't every one seeing your info if a solution is accepted with that database included in the solution.
Show which field determines recency (I assume a date field).
Refer to exact field names to join and to use. I can't find ordID.

List the expected result.

Ok.  See attached.  

The fields that should be linked are tblOpenOrders.RecID and tblFinished.OpenOrdRecID

And here is an example of what I would expect to see:

PartN       SumTotalOfScrap
9N429A              83

This is for just one Part_No in tblOpenOrders.  The sum total of 83 is from the last three highest numbered OpenOrdRecID in tblFinished.

This example is just for one part number because I cut the d/b back per your suggestion.  But in reality the result will be 4,126 resulting records using the whole database because that it how many different parts there are.

Does this help?
Query-Problem-2.zip

This is the idea, I'll check with your database.
Problem:
I have 2 tables a(aid), b(aid, bid, f1, seq)
seq is the field to sum for
f1 is the field showing the order
Join a(aid)__________ b(aid)

aID
1
2
3

aID      bID      f1      seq
1      1      1      2
1      2      2      2
1      3      3      3
1      4      4      3
2      1      5      4
2      2      6      4
2      3      7      5
3      1      8      2

Required result:

aid      SumOfSeq
1      8
2      13
3      2

Query:

Select aid, sum(seq) As SumOfSeq
FROM (Select a.aid, b.seq, b.f1 from a INNER JOIN b on a.aid=b.aid) as X
Where x.f1 IN (Select top 3 b.f1 from a inner join b on a.aid=b.aid Where x.aid=a.aid order by b.f1 desc)
Group by x.aid

Select allOpen in new window

No.


 how do you define the last 3 orders ?

Either by the highest RecID or Date_Entered

Check this query and comment:

SELECT x.PartN, Sum(x.QtyScrap) AS SumOfQtyScrap
FROM (SELECT tblFinished.PartN, tblFinished.QtyScrap, tblFinished.Date, tblOpenOrders.RecID  FROM  tblOpenOrders INNER JOIN tblFinished ON tblOpenOrders.RecID = tblFinished.OpenOrdRecID)  AS x
WHERE x.Date In (SELECT top 3 tblFinished.Date FROM tblOpenOrders INNER JOIN tblFinished ON tblOpenOrders.RecID = tblFinished.OpenOrdRecID
Where x.RecID= tblFinished.OpenOrdRecID ORDER BY tblFinished.Date Desc)
GROUP BY x.PartN;

Modify the field name that applies the order to records. In this example I assumed tblFinished.Date, then you need to modify the places where a.

As code snippet:

SELECT x.PartN, Sum(x.QtyScrap) AS SumOfQtyScrap
FROM (SELECT tblFinished.PartN, tblFinished.QtyScrap, tblFinished.Date, tblOpenOrders.RecID  FROM  tblOpenOrders INNER JOIN tblFinished ON tblOpenOrders.RecID = tblFinished.OpenOrdRecID)  AS x
WHERE x.Date In (SELECT top 3 tblFinished.Date FROM tblOpenOrders INNER JOIN tblFinished ON tblOpenOrders.RecID = tblFinished.OpenOrdRecID
Where x.RecID= tblFinished.OpenOrdRecID ORDER BY tblFinished.Date Desc)
GROUP BY x.PartN;

Select allOpen in new window

I think this is getting close but when I run the query in the file "Query Problem 2", I would still expect to see the result be 83 because that is the sum total of scrap in the last three orders.

If you open tblFinished and sort in desc order by Open Order Record ID and see the attachment in this reply you will see why I would expect 83 to be the result, not 111.  If you add up the numbers in the Qty Scrap column you get 83.  I just don't know how to change the SQL to make it come out right.
top-3-open-order-records.jpg

Hi.  Just checking.  Am I right that there should be 83 not 111?  I just don't know how to tweak it so I get 83.

Can you mark the last 3 records and how do you decide if they are the last 3?
It may turn up to be just changing the order by field.

It's not the last three records.  It is all of the records in tblFinished related to the last three records in tblOpenOrders.

Is order in tblOpenOrders controlled by Date_Entered field?
Are the last three records in table tblOpenOrders for PartN =9N429A are RecID: 28023, 26669, 23973?

Why part number is included in both tables?

No.  order in tblOpenOrders is controlled by the RecID.  So the last three orders are 26669, 23973, and 23904.

Its a legacy database which is why the part number  is in both fields.  Bad design.

Try this:
Create Query1:
SELECT a.Part_No, a.RecID
FROM tblOpenOrders AS a
WHERE (((a.RecID) In (Select Top 3 b.RecID From tblOpenOrders b Where b.Part_No=a.Part_No Order by b.RecID Desc)));

Create Query2:
SELECT Query01.Part_No, Sum(tblFinished.QtyScrap) AS SumOfQtyScrap1
FROM Query01 INNER JOIN tblFinished ON Query01.RecID = tblFinished.OpenOrdRecID
GROUP BY Query01.Part_No;

Query takes long time, but finally executes.

Try this database. Copy the queries to you original data and check.
Run query Q_Result
Query-Problem-x.accdb

I still don't think we're getting the right answer.  When I run q_result I get xx.  I think I shoul dbe getting 83.  Please see the attachement I sent in ID: 39853011.  The sum total in the Qty Scrap column is 83 with 26669, 23973, and 23904 being the last 3 open order number.

Try this modified version:

Queries:

'Q1 gets first last record
SELECT tblOpenOrders.Part_No, Max(tblOpenOrders.RecID) AS R1
FROM tblOpenOrders INNER JOIN tblFinished ON tblOpenOrders.RecID = tblFinished.OpenOrdRecID
GROUP BY tblOpenOrders.Part_No;

'Q2 gets second last record
SELECT tblOpenOrders.Part_No, Max(tblOpenOrders.RecID) AS R2
FROM Q1, tblOpenOrders INNER JOIN tblFinished ON tblOpenOrders.RecID = tblFinished.OpenOrdRecID
WHERE (((tblOpenOrders.Part_No)=Q1.Part_No) And ((tblOpenOrders.RecID)<Q1.R1))
GROUP BY tblOpenOrders.Part_No;

'Q3 gets third last record
SELECT tblOpenOrders.Part_No, Max(tblOpenOrders.RecID) AS R2
FROM Q2, tblOpenOrders INNER JOIN tblFinished ON tblOpenOrders.RecID = tblFinished.OpenOrdRecID
WHERE (((tblOpenOrders.Part_No)=Q2.Part_No) And ((tblOpenOrders.RecID)<Q2.R2))
GROUP BY tblOpenOrders.Part_No;

'Q4: Union Query of Q1, Q2, Q3
SELECT Part_No, R1 As RecID From Q1
UNION ALL
SELECT Part_No, R2 As RecID From Q2
UNION ALL 
SELECT Part_No, R2 As RecID From Q3;

'Q_Result
SELECT Q4.Part_No, Sum(tblFinished.QtyScrap) AS SumOfQtyScrap
FROM Q4 INNER JOIN tblFinished ON Q4.RecID = tblFinished.OpenOrdRecID
GROUP BY Q4.Part_No;

Select allOpen in new window

Query-Problem-y.accdb

Fantastic!!  Thank you.

Welcome!