Average of Cycle Time Per Month

G

Guest

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.
 
J

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


David J. said:
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.
 
M

Marshall Barton

David said:
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
 
G

Guest

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.
 
G

Guest

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.

Jeff Boyce said:
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


David J. said:
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.
 
M

Marshall Barton

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

GROUP BY Year(DateLotInDeptA), Month(DateLotInDeptA)
 
J

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

David J. said:
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.

Jeff Boyce said:
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


David J. said:
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.
 
G

Guest

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.

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

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


David J. said:
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.
 
M

Marshall Barton

This might take care that issue:

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

Guest

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

Marshall Barton said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top