asked on

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

dzakowski

ASKER

I guess I didn't put enough points as a reward...sorry, I'm still new at this

Jeffrey Coachman

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

dzakowski

ASKER

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?

Jeffrey Coachman

<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

dzakowski

ASKER

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

dzakowski

ASKER

the attachment didn't upload, here is the attachment for my last comment
Item-Lead-Time-Sample-Results.xlsx

Jeffrey Coachman

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

ASKER CERTIFIED SOLUTION

dzakowski

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jeffrey Coachman