Solved

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

Posted on 2014-02-11
27
330 Views
Last Modified: 2014-02-17
I need to design a query that will sum the values in a field named "ScrapQty for the last three orders in tblOpenOrders.

The tables are tblOpenOrders and tblFinished.  tblFinished is where the field named "ScrapQty" is.  They are linked via a field named "OpenOrderID"

I know how to sum all records but do not know how to sum just the 3 most recent records.

--Steve
0
Comment
Question by:SteveL13
  • 11
  • 11
  • 4
  • +1
27 Comments
 
LVL 34

Expert Comment

by:PatHartman
ID: 39851068
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.
0
 
LVL 30

Expert Comment

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

Author Comment

by:SteveL13
ID: 39851090
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.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39851150
so it will be clear, upload a sample db with the two tables...
0
 

Author Comment

by:SteveL13
ID: 39851271
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
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39851333
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..
0
 

Author Comment

by:SteveL13
ID: 39851372
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?
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39851410
open the table  tblOpenOrders, copy the last 3 orders and post here and explain why they are the last 3 orders.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 39851645
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.
0
 

Author Comment

by:SteveL13
ID: 39851749
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
0
 
LVL 30

Expert Comment

by:hnasr
ID: 39851783
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

Open in new window

0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39851787
No.


 how do you define the last 3 orders ?
0
 

Author Comment

by:SteveL13
ID: 39851820
Either by the highest RecID or Date_Entered
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 30

Expert Comment

by:hnasr
ID: 39851970
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;

Open in new window

0
 

Author Comment

by:SteveL13
ID: 39853011
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
0
 

Author Comment

by:SteveL13
ID: 39854692
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.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 39855252
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.
0
 

Author Comment

by:SteveL13
ID: 39855864
It's not the last three records.  It is all of the records in tblFinished related to the last three records in tblOpenOrders.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 39855913
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?
0
 

Author Comment

by:SteveL13
ID: 39855931
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.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 39856938
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.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 39857271
Try this database. Copy the queries to you original data and check.
Run query Q_Result
Query-Problem-x.accdb
0
 

Author Comment

by:SteveL13
ID: 39857392
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.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 39857800
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;

Open in new window

Query-Problem-y.accdb
0
 
LVL 30

Accepted Solution

by:
hnasr earned 500 total points
ID: 39858051
Try this version using just one query with nested sub queries. Run Q_Result_just_one_query
SELECT Q4.Part_No, Sum(tblFinished.QtyScrap) AS SumOfQtyScrap
FROM (SELECT Part_No, R1 As RecID From (SELECT tblOpenOrders.Part_No, Max(tblOpenOrders.RecID) AS R1
FROM tblOpenOrders INNER JOIN tblFinished ON tblOpenOrders.RecID = tblFinished.OpenOrdRecID
GROUP BY tblOpenOrders.Part_No) As Q1
UNION ALL
SELECT Part_No, R2 As RecID From (SELECT tblOpenOrders.Part_No, Max(tblOpenOrders.RecID) AS R2
FROM (SELECT tblOpenOrders.Part_No, Max(tblOpenOrders.RecID) AS R1
FROM tblOpenOrders INNER JOIN tblFinished ON tblOpenOrders.RecID = tblFinished.OpenOrdRecID
GROUP BY tblOpenOrders.Part_No
) As 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) As Q2
UNION ALL 
SELECT Part_No, R2 As RecID From (SELECT tblOpenOrders.Part_No, Max(tblOpenOrders.RecID) AS R2
FROM (SELECT tblOpenOrders.Part_No, Max(tblOpenOrders.RecID) AS R2
FROM (SELECT tblOpenOrders.Part_No, Max(tblOpenOrders.RecID) AS R1
FROM tblOpenOrders INNER JOIN tblFinished ON tblOpenOrders.RecID = tblFinished.OpenOrdRecID
GROUP BY tblOpenOrders.Part_No
) As 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
) As 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) As Q3)  AS Q4 INNER JOIN tblFinished ON Q4.RecID = tblFinished.OpenOrdRecID
GROUP BY Q4.Part_No;

Open in new window

Query-Problem-z-.accdb
0
 

Author Closing Comment

by:SteveL13
ID: 39865389
Fantastic!!  Thank you.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 39866489
Welcome!
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now