Average of Cycle Time Per Month

[David J.]

Okay, I tried reading the other average posts, and I still can't figure out
what to do. The database I am using tracks when various lots were received,
when the lot went to department A, and when it left department A. Therefore,
the table has a lot number, and 3 dates.

Lot# DateLotRecvd DateLotInDeptA DateLotOutDeptA

There are multiple lots for each month. I need to know the Cycle Time for In
to Out. That is easy, [tblMain.DateLotOutDeptA]-[tblMain.DateLotInDeptA].
Although I know this, I need to calculate the average of the Cycle Times for
each month. The month is based off of the DateLotInDeptA.

For example...

Lot# DateLotRecvd DateLotInDeptA DateLotOutDeptA Cycle Time
123 03-May-07 06-May-07 10-May-07 4
456 03-May-07 10-May-07 12-May-07 2
567 03-May-07 12-May-07 28-May-07 16
789 03-May-07 20-May-07 30-May-07 10
157 03-May-07 28-May-07 2-Jun-07 5
834 03-May-07 08-Jun-07 13-Jun-07 5

Now, from this data I would need to calculate the the average of the lead
times for the month pertaining to DateLotInDeptA. This means the all of the
cycle times except the last one, as the DateLotInDeptA was in June.

So, my question is 2 parts. I need to be able to sum up the Cycle Times for
a specific month, then take the average of those times by dividing the number
of lots per that month. Then I will need to display a query that will show
the month and the average cycle time, which would look like this...

Month AvgCycleTime
Feb X
Mar Y
Apr Z
May A
Jun B

I hope this makes sense. I literally have no clue how to get this solution.
I can get the cycle times individually, but not get the averages or display
averages per a given month. If you need more explanations, let me know.
Thanks a ton for any help.

 

[Jeff Boyce]

David

It sounds like you are saying that you want to group by the month of the
date received, then average the Out-In.

First, what will you do when you have more than one year's data, so that you
have TWO different January's? (and if you say "start another db or table,
you are still in spreadsheet mode!)

Second, naming a field "DateLotInDeptA" could imply that you also have a
field named "DateLotInDeptB" and "DateLotInDeptC", and ... If so, you have
a spreadsheet!

If you decide you still want to do this, you can start a new query, add in
your table, then add new fields, something like:

NewField: Month([DateLotRecvd])
and
Difference: DateDiff("d",[DateLotOutDeptA]-[DateLotInDeptA])

Now convert the query to a Totals query, GroupBy that NewField and Average
on the Difference field.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Okay, I tried reading the other average posts, and I still can't figure
out
what to do. The database I am using tracks when various lots were
received,
when the lot went to department A, and when it left department A.
Therefore,
the table has a lot number, and 3 dates.

Lot# DateLotRecvd DateLotInDeptA DateLotOutDeptA

There are multiple lots for each month. I need to know the Cycle Time for
In
to Out. That is easy, [tblMain.DateLotOutDeptA]-[tblMain.DateLotInDeptA].
Although I know this, I need to calculate the average of the Cycle Times
for
each month. The month is based off of the DateLotInDeptA.

For example...

Lot# DateLotRecvd DateLotInDeptA DateLotOutDeptA Cycle Time
123 03-May-07 06-May-07 10-May-07 4
456 03-May-07 10-May-07 12-May-07 2
567 03-May-07 12-May-07 28-May-07 16
789 03-May-07 20-May-07 30-May-07 10
157 03-May-07 28-May-07 2-Jun-07 5
834 03-May-07 08-Jun-07 13-Jun-07 5

Now, from this data I would need to calculate the the average of the lead
times for the month pertaining to DateLotInDeptA. This means the all of
the
cycle times except the last one, as the DateLotInDeptA was in June.

So, my question is 2 parts. I need to be able to sum up the Cycle Times
for
a specific month, then take the average of those times by dividing the
number
of lots per that month. Then I will need to display a query that will show
the month and the average cycle time, which would look like this...

Month AvgCycleTime
Feb X
Mar Y
Apr Z
May A
Jun B

I hope this makes sense. I literally have no clue how to get this
solution.
I can get the cycle times individually, but not get the averages or
display
averages per a given month. If you need more explanations, let me know.
Thanks a ton for any help.

 

[Marshall Barton]

Okay, I tried reading the other average posts, and I still can't figure out
what to do. The database I am using tracks when various lots were received,
when the lot went to department A, and when it left department A. Therefore,
the table has a lot number, and 3 dates.

Lot# DateLotRecvd DateLotInDeptA DateLotOutDeptA

There are multiple lots for each month. I need to know the Cycle Time for In
to Out. That is easy, [tblMain.DateLotOutDeptA]-[tblMain.DateLotInDeptA].
Although I know this, I need to calculate the average of the Cycle Times for
each month. The month is based off of the DateLotInDeptA.

For example...

Lot# DateLotRecvd DateLotInDeptA DateLotOutDeptA Cycle Time
123 03-May-07 06-May-07 10-May-07 4
456 03-May-07 10-May-07 12-May-07 2
567 03-May-07 12-May-07 28-May-07 16
789 03-May-07 20-May-07 30-May-07 10
157 03-May-07 28-May-07 2-Jun-07 5
834 03-May-07 08-Jun-07 13-Jun-07 5

Now, from this data I would need to calculate the the average of the lead
times for the month pertaining to DateLotInDeptA. This means the all of the
cycle times except the last one, as the DateLotInDeptA was in June.

So, my question is 2 parts. I need to be able to sum up the Cycle Times for
a specific month, then take the average of those times by dividing the number
of lots per that month. Then I will need to display a query that will show
the month and the average cycle time, which would look like this...

Month AvgCycleTime
Feb X
Mar Y
Apr Z
May A
Jun B

Does this kind of query do what you want?

SELECT Year(DateLotInDeptA) As YearIn,
Month(DateLotInDeptA) As MonthIn,
Avg(DateDiff("d", DateLotInDeptA, DateLotOutDeptA))
As AvgCycleTime
FROM sometable
GROUP BY Year(DateLotInDeptA) As YearIn,
Month(DateLotInDeptA) As MonthIn

 

[David J.]

The query you wrote makes sense, but I am getting the error "Syntax Error
(missing operator) in query expression Year(DateLotInDeptA) As YearIn." When
I click OK on the message, it brings me back to the SQL and has the "As"
highlighted in the GROUP BY statement line. I tried a couple things, still
got the same error.

I am using MS Access 2003 if that makes any difference at all. Thanks again
for any additional help.

 

[David J.]

Just to address a couple things. I know I can do this in Excel, would take me
about 5 minutes to do everything I need. However, the data needs to go into
Access, as I do not want to make a macro to send info from Access to Excel.
There is only one department, guess I shouldn't have used 'A'. I do have more
than 1 year's data, but I only need to display 1 year at a time, so I would
have a filter for that, as I do with all of my other queries. Also, what do
you mean a Totals query? Thanks.

David

It sounds like you are saying that you want to group by the month of the
date received, then average the Out-In.

First, what will you do when you have more than one year's data, so that you
have TWO different January's? (and if you say "start another db or table,
you are still in spreadsheet mode!)

Second, naming a field "DateLotInDeptA" could imply that you also have a
field named "DateLotInDeptB" and "DateLotInDeptC", and ... If so, you have
a spreadsheet!

If you decide you still want to do this, you can start a new query, add in
your table, then add new fields, something like:

NewField: Month([DateLotRecvd])
and
Difference: DateDiff("d",[DateLotOutDeptA]-[DateLotInDeptA])

Now convert the query to a Totals query, GroupBy that NewField and Average
on the Difference field.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Okay, I tried reading the other average posts, and I still can't figure
out
what to do. The database I am using tracks when various lots were
received,
when the lot went to department A, and when it left department A.
Therefore,
the table has a lot number, and 3 dates.

Lot# DateLotRecvd DateLotInDeptA DateLotOutDeptA

There are multiple lots for each month. I need to know the Cycle Time for
In
to Out. That is easy, [tblMain.DateLotOutDeptA]-[tblMain.DateLotInDeptA].
Although I know this, I need to calculate the average of the Cycle Times
for
each month. The month is based off of the DateLotInDeptA.

For example...

Lot# DateLotRecvd DateLotInDeptA DateLotOutDeptA Cycle Time
123 03-May-07 06-May-07 10-May-07 4
456 03-May-07 10-May-07 12-May-07 2
567 03-May-07 12-May-07 28-May-07 16
789 03-May-07 20-May-07 30-May-07 10
157 03-May-07 28-May-07 2-Jun-07 5
834 03-May-07 08-Jun-07 13-Jun-07 5

Now, from this data I would need to calculate the the average of the lead
times for the month pertaining to DateLotInDeptA. This means the all of
the
cycle times except the last one, as the DateLotInDeptA was in June.

So, my question is 2 parts. I need to be able to sum up the Cycle Times
for
a specific month, then take the average of those times by dividing the
number
of lots per that month. Then I will need to display a query that will show
the month and the average cycle time, which would look like this...

Month AvgCycleTime
Feb X
Mar Y
Apr Z
May A
Jun B

I hope this makes sense. I literally have no clue how to get this
solution.
I can get the cycle times individually, but not get the averages or
display
averages per a given month. If you need more explanations, let me know.
Thanks a ton for any help.

 

[Marshall Barton]

Oh bleep. That's a copy/paste fubar, it should have been:

GROUP BY Year(DateLotInDeptA), Month(DateLotInDeptA)

 

[Jeff Boyce]

David

Create a new query. Add the table and fields. Click on the button with the
Greek 'sigma' character on it to convert the query to a Totals query. Check
Access HELP for more info about Totals queries.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Just to address a couple things. I know I can do this in Excel, would take
me
about 5 minutes to do everything I need. However, the data needs to go
into
Access, as I do not want to make a macro to send info from Access to
Excel.
There is only one department, guess I shouldn't have used 'A'. I do have
more
than 1 year's data, but I only need to display 1 year at a time, so I
would
have a filter for that, as I do with all of my other queries. Also, what
do
you mean a Totals query? Thanks.

David

It sounds like you are saying that you want to group by the month of the
date received, then average the Out-In.

First, what will you do when you have more than one year's data, so that
you
have TWO different January's? (and if you say "start another db or
table,
you are still in spreadsheet mode!)

Second, naming a field "DateLotInDeptA" could imply that you also have a
field named "DateLotInDeptB" and "DateLotInDeptC", and ... If so, you
have
a spreadsheet!

If you decide you still want to do this, you can start a new query, add
in
your table, then add new fields, something like:

NewField: Month([DateLotRecvd])
and
Difference: DateDiff("d",[DateLotOutDeptA]-[DateLotInDeptA])

Now convert the query to a Totals query, GroupBy that NewField and
Average
on the Difference field.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Okay, I tried reading the other average posts, and I still can't figure
out
what to do. The database I am using tracks when various lots were
received,
when the lot went to department A, and when it left department A.
Therefore,
the table has a lot number, and 3 dates.

Lot# DateLotRecvd DateLotInDeptA DateLotOutDeptA

There are multiple lots for each month. I need to know the Cycle Time
for
In
to Out. That is easy,
[tblMain.DateLotOutDeptA]-[tblMain.DateLotInDeptA].
Although I know this, I need to calculate the average of the Cycle
Times
for
each month. The month is based off of the DateLotInDeptA.

For example...

Lot# DateLotRecvd DateLotInDeptA DateLotOutDeptA Cycle Time
123 03-May-07 06-May-07 10-May-07 4
456 03-May-07 10-May-07 12-May-07 2
567 03-May-07 12-May-07 28-May-07 16
789 03-May-07 20-May-07 30-May-07 10
157 03-May-07 28-May-07 2-Jun-07 5
834 03-May-07 08-Jun-07 13-Jun-07 5

Now, from this data I would need to calculate the the average of the
lead
times for the month pertaining to DateLotInDeptA. This means the all of
the
cycle times except the last one, as the DateLotInDeptA was in June.

So, my question is 2 parts. I need to be able to sum up the Cycle Times
for
a specific month, then take the average of those times by dividing the
number
of lots per that month. Then I will need to display a query that will
show
the month and the average cycle time, which would look like this...

Month AvgCycleTime
Feb X
Mar Y
Apr Z
May A
Jun B

I hope this makes sense. I literally have no clue how to get this
solution.
I can get the cycle times individually, but not get the averages or
display
averages per a given month. If you need more explanations, let me know.
Thanks a ton for any help.

 

[David J.]

Works perfectly. Thanks. Now, 1 more question, as I figured it would be
better to ask here than start a new thread.

The number results I get from the Avg(DateDiff("d", DateLotInDeptA,
DateLotOutDeptA)) have lots of decimal places. How do I only have it display
Whole numbers? I am ultimately exporting the information into a Data Access
Page in an Access Graph. I have to label each column, so it gets really
crammed with a number like 3.45212343 spanning across 3 other results.

Oh bleep. That's a copy/paste fubar, it should have been:

GROUP BY Year(DateLotInDeptA), Month(DateLotInDeptA)
--
Marsh
MVP [MS Access]

The query you wrote makes sense, but I am getting the error "Syntax Error
(missing operator) in query expression Year(DateLotInDeptA) As YearIn." When
I click OK on the message, it brings me back to the SQL and has the "As"
highlighted in the GROUP BY statement line. I tried a couple things, still
got the same error.

I am using MS Access 2003 if that makes any difference at all. Thanks again
for any additional help.

 

[Marshall Barton]

This might take care that issue:

Round(Avg(DateDiff("d", DateLotInDeptA, DateLotOutDeptA)),0)

 

[David J.]

You are awesome! Thanks again for all of the help.

This might take care that issue:

Round(Avg(DateDiff("d", DateLotInDeptA, DateLotOutDeptA)),0)

--
Marsh
MVP [MS Access]

Works perfectly. Thanks. Now, 1 more question, as I figured it would be
better to ask here than start a new thread.

The number results I get from the Avg(DateDiff("d", DateLotInDeptA,
DateLotOutDeptA)) have lots of decimal places. How do I only have it display
Whole numbers? I am ultimately exporting the information into a Data Access
Page in an Access Graph. I have to label each column, so it gets really
crammed with a number like 3.45212343 spanning across 3 other results.