• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 320
  • Last Modified:

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

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
dzakowski
Asked:
dzakowski
  • 6
  • 4
1 Solution
 
dzakowskiAuthor Commented:
I guess I didn't put enough points as a reward...sorry, I'm still new at this
0
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
dzakowskiAuthor Commented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Jeffrey CoachmanMIS LiasonCommented:
<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
 
dzakowskiAuthor Commented:
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
 
dzakowskiAuthor Commented:
the attachment didn't upload, here is the attachment for my last comment
Item-Lead-Time-Sample-Results.xlsx
0
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
dzakowskiAuthor Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
OK, you can now accept your own post as the solution...
;-)

Jeff
0
 
dzakowskiAuthor Commented:
The experts were helpful but not able to fix this problem for me.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now