[staticz]

I am having some trouble trying to figure out a way to actually create this query. The data is stored in Access 2007 and maybe there is a better way of doing it, rather than a query, anyways.

Basically the table has every purchase order placed in the last year along with the part number, description, accountnum, orderdate, and recieve date.

Now, We are trying to figure out a way to calculate our average lead time on these orders for one year, 6months, and 3months. The lead time would be our orderdate - recievedate. My problem is calculating the average lead times.

Here is what I have so far.

SELECT partNum, description, accountNo, DateDiff("d",poDate,dateRecvd) AS leadTime
FROM Leadtime;

Anyone have any ideas how to calculate the average leadTime and only show one record of each part?

[xerxes333]

Im not sure how you would get all 3,6,12 month results in one table other than union-ing separate tables but here is an example of how to get the average over the past 180 days(6 months)

select sum(datediff(d,poDate,dateRecvd))/count(*) from Leadtime where dateRecvd>getdate()-180

[staticz]

Thanks for your help. I am still running into problems though, my biggest issue is figuring out a way to calculate the average for each part.

example:
We ordered #005 five times last year, with lead times of 5,2,8,10,and 24.
And we ordered #110 three times last year with lead times of 8,15, and 30.

Is there anyway I can write the query to only calculate the average for every part separately when the information for all the parts is stored in the same table?

This post has been edited by staticz: 23 January 2009 - 02:00 PM

[xerxes333]

Just do some grouping on the query I posted

select partNum, sum(datediff(d,poDate,dateRecvd))/count(*) from Leadtime where dateRecvd>getdate()-180 
group by partNum

This post has been edited by xerxes333: 23 January 2009 - 02:07 PM