[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Access query to take the average lead time of the last 3 receipts per item

Posted on 2011-10-17
10
Medium Priority
?
295 Views
Last Modified: 2012-05-12
I have a list of inventory receipt records, see attached spreadsheet for sample data.  I'm trying to write a query to take the average of the last 3 receipts per item.

Here is how far I've gotten, I have an average of actual_lead_days over the last year...how do I change the sql code to only average the last 3 receipts per item?

Here's my code so far:

SELECT dbo_item_lead_time.inv_mast_uid, dbo_item_lead_time.actual_lead_days, dbo_item_lead_time.receipt_date
FROM dbo_item_lead_time
WHERE (((dbo_item_lead_time.inv_mast_uid) Is Not Null) AND ((dbo_item_lead_time.receipt_date) Between Date()-365 And Date()))
ORDER BY dbo_item_lead_time.inv_mast_uid;

Item-Lead-Time.xlsx
0
Comment
Question by:dzakowski
  • 6
  • 4
10 Comments
 

Author Comment

by:dzakowski
ID: 36983204
I guess I didn't put enough points as a reward...sorry, I'm still new at this
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36983409
Define "Last"...?

Last by inv_mast_uid?, ...or last by receipt_date?

Because both of these fields in your table have duplicate values, your question is not clear...

You table needs an auto-incrementing ID field (autonumber) that will be the clear indicator of the Order.
Then you can average LeadTime of the "Last 3" ID values of this new field.

Something like this:

SELECT Avg(actual_lead_days) AS Avg3Last
FROM (SELECT TOP 3 Item_Lead_Time.ID, Item_Lead_Time.actual_lead_days
FROM Item_Lead_Time
ORDER BY Item_Lead_Time.ID DESC)  AS Item_Lead_Time;


See this sample

Access-SQL--EEQ27400801--SQL-Que.mdb
0
 

Author Comment

by:dzakowski
ID: 36983745
I tried your solution and I've having trouble adapting it to my data.  

I'm looking for the most recent 3 receipt_dates for each inv_mast_uid, not the average of the whole table.  Does that answer your question?
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36986596
<I tried your solution and I've having trouble adapting it to my data.  >
My solution uses the data you supplied...
All I did way bring it into a table in MS Access

<I'm looking for the most recent 3 receipt_dates for each inv_mast_uid, not the average of the whole table.>
My solution *Does* average only the last three dates.
...But again, as I stated, ...since inv_mast_uid contains repeating values, you really need to insert an autoincrementing field so that the *true* last 3 values can be averaged...
This is what my sample does...


JeffCoachman
0
 

Author Comment

by:dzakowski
ID: 36986812
Dear Jeff,
I understand that you brought it into access, my problem is that your query takes the last 3 receipt dates for the whole table.  What I'm looking for is the last 3 receipt dates for each unique inv_mast_uid.

I manually created a couple lines of the expected result and attached in excel to make sure that I explained it clearly.

Let me know if you have any questions.

Thanks,
Daniel
0
 

Author Comment

by:dzakowski
ID: 36986872
the attachment didn't upload, here is the attachment for my last comment
Item-Lead-Time-Sample-Results.xlsx
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36986927
Then this is slightly beyond me.

Please click the "Request Attention" link and ask that the SQL Syntax zone be added to this Q.

Jeff
0
 

Accepted Solution

by:
dzakowski earned 0 total points
ID: 36987638
FYI - i patched together some stuff i found on other websites and here's what i got to work....in case you're interested


SELECT (select count(*) from Item_Lead_Time as X where inv_mast_uid=Item_Lead_Time.inv_mast_uid and receipt_date>=Item_Lead_Time.receipt_date) AS sequence, Item_Lead_Time.inv_mast_uid, Item_Lead_Time.receipt_date
FROM Item_Lead_Time
WHERE ((((select count(*) from Item_Lead_Time as X where inv_mast_uid=Item_Lead_Time.inv_mast_uid and receipt_date>=Item_Lead_Time.receipt_date))=1 Or ((select count(*) from Item_Lead_Time as X where inv_mast_uid=Item_Lead_Time.inv_mast_uid and receipt_date>=Item_Lead_Time.receipt_date))=2 Or ((select count(*) from Item_Lead_Time as X where inv_mast_uid=Item_Lead_Time.inv_mast_uid and receipt_date>=Item_Lead_Time.receipt_date))=3))
ORDER BY Item_Lead_Time.inv_mast_uid, Item_Lead_Time.receipt_date;
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36990654
OK, you can now accept your own post as the solution...
;-)

Jeff
0
 

Author Closing Comment

by:dzakowski
ID: 37043506
The experts were helpful but not able to fix this problem for me.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

873 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