Solved

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

Posted on 2014-02-11
27
334 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 35

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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

840 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