Equipment Utilization

  • Thread starter Thread starter Russ via AccessMonster.com
  • Start date Start date
R

Russ via AccessMonster.com

Equipment Utilization
Ok, I need to figure out equipment utilization, the table I have is the
shipping table with the ship date and receive date.
The problem I have is if it shipped out on 7-1-05 and returned 7-1-06 how do
you split it per year 6 months in 2005 and 6 months in 2006?
Any help would be greatly appreciated.
 
Dear Russ:

Not very simply, but not too bad.

Let's create a table AnnualRange:

2005 1/1/2005 12/31/2005
2006 1/1/2006 12/31/2006

Put more years than just these in the table.

Is the "ship date" always less than the "receive date". That's the way I
understand this part.

A complete solution would also take usage out on 3/13/2002 and returning
3/13/2006 to have 5 parts:

3/13/2002 to 12/31/2002
1/1/2003 to 12/31/2003
1/1/2004 to 12/31/2004
1/1/2005 to 12/31/2005
1/1/2006 to 3/13/2006

I start with a cross product of the two tables, then eliminate those rows
not appropriate.

SELECT A.CYear, A.BeginDate, A.EndDate, B.ShipOut, B.ReturnIn
FROM AnnualRange A, EquipUtil B

The above reveals the tables I created to test this:

EquipUtil
ShipOut date/time
ReturnIn date/time

The AnnualRange table is as shown above.

The finished query:

SELECT A.CYear, A.BeginDate, A.EndDate, B.ShipOut, B.ReturnIn,
IIf(A.BeginDate < B.ShipOut, B.ShipOut, A.BeginDate) AS BeginMe,
IIf(A.EndDate > B.ReturnIn, B.ReturnIn, A.EndDate) AS EndMe
FROM AnnualRange A, EquipUtil B
WHERE A.BeginDate <= B.ReturnIn
AND A.EndDate >= B.ShipOut

You will need to adapt this to your table and column names.

Here's the test data and results:

AnnualRange CYear BeginDate EndDate
2000 1/1/2000 12/31/2000
2001 1/1/2001 12/31/2001
2002 1/1/2002 12/31/2002
2003 1/1/2003 12/31/2003
2004 1/1/2004 12/31/2004
2005 1/1/2005 12/31/2005
2006 1/1/2006 12/31/2006


EquipUtil ShipOut ReturnIn
3/13/2001 5/15/2005


MQry CYear BeginDate EndDate ShipOut ReturnIn BeginMe EndMe
2001 1/1/2001 12/31/2001 3/13/2001 5/15/2005 3/13/2001 12/31/2001
2002 1/1/2002 12/31/2002 3/13/2001 5/15/2005 1/1/2002 12/31/2002
2003 1/1/2003 12/31/2003 3/13/2001 5/15/2005 1/1/2003 12/31/2003
2004 1/1/2004 12/31/2004 3/13/2001 5/15/2005 1/1/2004 12/31/2004
2005 1/1/2005 12/31/2005 3/13/2001 5/15/2005 1/1/2005 5/15/2005


The equipment removed 3/13/2001 and returned 5/15/2005 returns 5 rows, the
first and last being for partial years.

I believe this is what you want. If you have trouble adjusting this to your
data, please fill in what that data is so I can make the adjustments.

Tom Ellison
 
Russ said:
Equipment Utilization
Ok, I need to figure out equipment utilization, the table I have is the
shipping table with the ship date and receive date.
The problem I have is if it shipped out on 7-1-05 and returned 7-1-06 how do
you split it per year 6 months in 2005 and 6 months in 2006?
Any help would be greatly appreciated.
Click to expand...

You can do this by first checking if they are in the same year, and if
so, subtract months (or days or whatever utilization method you want).
If not, determine their months based on the end of the year (month 12).

You'll need to adapt this to suit your needs for determine the
difference between dates.

For example

=IIf(Year([DateOut]) = Year ([DateReturned]), Month([DateReturned] -
Month([DateOut]), (12 - Month([DateReturned])) + (12 - Month([DateOut])))
 
Thanks, I will give these a try.

Duncan said:
Equipment Utilization
Ok, I need to figure out equipment utilization, the table I have is the
shipping table with the ship date and receive date.
The problem I have is if it shipped out on 7-1-05 and returned 7-1-06 how do
you split it per year 6 months in 2005 and 6 months in 2006?
Any help would be greatly appreciated.
Click to expand...

You can do this by first checking if they are in the same year, and if
so, subtract months (or days or whatever utilization method you want).
If not, determine their months based on the end of the year (month 12).

You'll need to adapt this to suit your needs for determine the
difference between dates.

For example

=IIf(Year([DateOut]) = Year ([DateReturned]), Month([DateReturned] -
Month([DateOut]), (12 - Month([DateReturned])) + (12 - Month([DateOut])))
Click to expand...
 
Ok, I tried this and it works of if the ship date and the return date are in
the same year, but if the return date is in the following year it still
counts all the time. Some how it needs to stop at the end of the year and
then start from the beginning of the next year and give day dif for that,
hope I am explaining this ok?

Days: IIf(Year([ShipDate])=Year([ReturnDate]),DateDiff("d",[ShipDate],(
[ReturnDate])),(12-Month([ReturnDate]))+(12-Month([ShipDate])))

Duncan said:
Equipment Utilization
Ok, I need to figure out equipment utilization, the table I have is the
shipping table with the ship date and receive date.
The problem I have is if it shipped out on 7-1-05 and returned 7-1-06 how do
you split it per year 6 months in 2005 and 6 months in 2006?
Any help would be greatly appreciated.
Click to expand...

You can do this by first checking if they are in the same year, and if
so, subtract months (or days or whatever utilization method you want).
If not, determine their months based on the end of the year (month 12).

You'll need to adapt this to suit your needs for determine the
difference between dates.

For example

=IIf(Year([DateOut]) = Year ([DateReturned]), Month([DateReturned] -
Month([DateOut]), (12 - Month([DateReturned])) + (12 - Month([DateOut])))
Click to expand...
 
Dear Russ:

May I point out some advantages here?

The method here will break the time equipment is utilized into as many
annual periods as necessary.

It can be modified to non-calendar years, quarters, months, or whatever is
desired.

Tom Ellison


Tom Ellison said:
Dear Russ:

Not very simply, but not too bad.

Let's create a table AnnualRange:

2005 1/1/2005 12/31/2005
2006 1/1/2006 12/31/2006

Put more years than just these in the table.

Is the "ship date" always less than the "receive date". That's the way I
understand this part.

A complete solution would also take usage out on 3/13/2002 and returning
3/13/2006 to have 5 parts:

3/13/2002 to 12/31/2002
1/1/2003 to 12/31/2003
1/1/2004 to 12/31/2004
1/1/2005 to 12/31/2005
1/1/2006 to 3/13/2006

I start with a cross product of the two tables, then eliminate those rows
not appropriate.

SELECT A.CYear, A.BeginDate, A.EndDate, B.ShipOut, B.ReturnIn
FROM AnnualRange A, EquipUtil B

The above reveals the tables I created to test this:

EquipUtil
ShipOut date/time
ReturnIn date/time

The AnnualRange table is as shown above.

The finished query:

SELECT A.CYear, A.BeginDate, A.EndDate, B.ShipOut, B.ReturnIn,
IIf(A.BeginDate < B.ShipOut, B.ShipOut, A.BeginDate) AS BeginMe,
IIf(A.EndDate > B.ReturnIn, B.ReturnIn, A.EndDate) AS EndMe
FROM AnnualRange A, EquipUtil B
WHERE A.BeginDate <= B.ReturnIn
AND A.EndDate >= B.ShipOut

You will need to adapt this to your table and column names.

Here's the test data and results:

AnnualRange CYear BeginDate EndDate
2000 1/1/2000 12/31/2000
2001 1/1/2001 12/31/2001
2002 1/1/2002 12/31/2002
2003 1/1/2003 12/31/2003
2004 1/1/2004 12/31/2004
2005 1/1/2005 12/31/2005
2006 1/1/2006 12/31/2006


EquipUtil ShipOut ReturnIn
3/13/2001 5/15/2005


MQry CYear BeginDate EndDate ShipOut ReturnIn BeginMe EndMe
2001 1/1/2001 12/31/2001 3/13/2001 5/15/2005 3/13/2001 12/31/2001
2002 1/1/2002 12/31/2002 3/13/2001 5/15/2005 1/1/2002 12/31/2002
2003 1/1/2003 12/31/2003 3/13/2001 5/15/2005 1/1/2003 12/31/2003
2004 1/1/2004 12/31/2004 3/13/2001 5/15/2005 1/1/2004 12/31/2004
2005 1/1/2005 12/31/2005 3/13/2001 5/15/2005 1/1/2005 5/15/2005


The equipment removed 3/13/2001 and returned 5/15/2005 returns 5 rows, the
first and last being for partial years.

I believe this is what you want. If you have trouble adjusting this to
your data, please fill in what that data is so I can make the adjustments.

Tom Ellison
Click to expand...
 
Tom,
It looks like you understand what I want to do, but I am having trouble
following your example, sorry still a little new at this…
Is this all done with queries? Select / append ????


Tom said:
Dear Russ:

Not very simply, but not too bad.

Let's create a table AnnualRange:

2005 1/1/2005 12/31/2005
2006 1/1/2006 12/31/2006

Put more years than just these in the table.

Is the "ship date" always less than the "receive date". That's the way I
understand this part.

A complete solution would also take usage out on 3/13/2002 and returning
3/13/2006 to have 5 parts:

3/13/2002 to 12/31/2002
1/1/2003 to 12/31/2003
1/1/2004 to 12/31/2004
1/1/2005 to 12/31/2005
1/1/2006 to 3/13/2006

I start with a cross product of the two tables, then eliminate those rows
not appropriate.

SELECT A.CYear, A.BeginDate, A.EndDate, B.ShipOut, B.ReturnIn
FROM AnnualRange A, EquipUtil B

The above reveals the tables I created to test this:

EquipUtil
ShipOut date/time
ReturnIn date/time

The AnnualRange table is as shown above.

The finished query:

SELECT A.CYear, A.BeginDate, A.EndDate, B.ShipOut, B.ReturnIn,
IIf(A.BeginDate < B.ShipOut, B.ShipOut, A.BeginDate) AS BeginMe,
IIf(A.EndDate > B.ReturnIn, B.ReturnIn, A.EndDate) AS EndMe
FROM AnnualRange A, EquipUtil B
WHERE A.BeginDate <= B.ReturnIn
AND A.EndDate >= B.ShipOut

You will need to adapt this to your table and column names.

Here's the test data and results:

AnnualRange CYear BeginDate EndDate
2000 1/1/2000 12/31/2000
2001 1/1/2001 12/31/2001
2002 1/1/2002 12/31/2002
2003 1/1/2003 12/31/2003
2004 1/1/2004 12/31/2004
2005 1/1/2005 12/31/2005
2006 1/1/2006 12/31/2006

EquipUtil ShipOut ReturnIn
3/13/2001 5/15/2005

MQry CYear BeginDate EndDate ShipOut ReturnIn BeginMe EndMe
2001 1/1/2001 12/31/2001 3/13/2001 5/15/2005 3/13/2001 12/31/2001
2002 1/1/2002 12/31/2002 3/13/2001 5/15/2005 1/1/2002 12/31/2002
2003 1/1/2003 12/31/2003 3/13/2001 5/15/2005 1/1/2003 12/31/2003
2004 1/1/2004 12/31/2004 3/13/2001 5/15/2005 1/1/2004 12/31/2004
2005 1/1/2005 12/31/2005 3/13/2001 5/15/2005 1/1/2005 5/15/2005

The equipment removed 3/13/2001 and returned 5/15/2005 returns 5 rows, the
first and last being for partial years.

I believe this is what you want. If you have trouble adjusting this to your
data, please fill in what that data is so I can make the adjustments.

Tom Ellison
Equipment Utilization
Ok, I need to figure out equipment utilization, the table I have is the
Click to expand...
[quoted text clipped - 3 lines]
you split it per year 6 months in 2005 and 6 months in 2006?
Any help would be greatly appreciated.
Click to expand...
Click to expand...
 
Dear Russ:

You need two things:

- a table which I described

- a query like what I gave you

The only query is the SELECT query I sent. The entries in the table were
typed manually. They could also be created in an automated fashion.

In the results from my example, there's a lot more information than just
what you need. Maybe that's confusing. More simply:

MQry CYear BeginMe EndMe
2001 3/13/2001 12/31/2001
2002 1/1/2002 12/31/2002
2003 1/1/2003 12/31/2003
2004 1/1/2004 12/31/2004
2005 1/1/2005 5/15/2005


An item that went out 3/31/2001 and returned 5/15/2005 gives the results
shown.

Tom Ellison


Russ via AccessMonster.com said:
Tom,
It looks like you understand what I want to do, but I am having trouble
following your example, sorry still a little new at this.
Is this all done with queries? Select / append ????


Tom said:
Dear Russ:

Not very simply, but not too bad.

Let's create a table AnnualRange:

2005 1/1/2005 12/31/2005
2006 1/1/2006 12/31/2006

Put more years than just these in the table.

Is the "ship date" always less than the "receive date". That's the way I
understand this part.

A complete solution would also take usage out on 3/13/2002 and returning
3/13/2006 to have 5 parts:

3/13/2002 to 12/31/2002
1/1/2003 to 12/31/2003
1/1/2004 to 12/31/2004
1/1/2005 to 12/31/2005
1/1/2006 to 3/13/2006

I start with a cross product of the two tables, then eliminate those rows
not appropriate.

SELECT A.CYear, A.BeginDate, A.EndDate, B.ShipOut, B.ReturnIn
FROM AnnualRange A, EquipUtil B

The above reveals the tables I created to test this:

EquipUtil
ShipOut date/time
ReturnIn date/time

The AnnualRange table is as shown above.

The finished query:

SELECT A.CYear, A.BeginDate, A.EndDate, B.ShipOut, B.ReturnIn,
IIf(A.BeginDate < B.ShipOut, B.ShipOut, A.BeginDate) AS BeginMe,
IIf(A.EndDate > B.ReturnIn, B.ReturnIn, A.EndDate) AS EndMe
FROM AnnualRange A, EquipUtil B
WHERE A.BeginDate <= B.ReturnIn
AND A.EndDate >= B.ShipOut

You will need to adapt this to your table and column names.

Here's the test data and results:

AnnualRange CYear BeginDate EndDate
2000 1/1/2000 12/31/2000
2001 1/1/2001 12/31/2001
2002 1/1/2002 12/31/2002
2003 1/1/2003 12/31/2003
2004 1/1/2004 12/31/2004
2005 1/1/2005 12/31/2005
2006 1/1/2006 12/31/2006

EquipUtil ShipOut ReturnIn
3/13/2001 5/15/2005

MQry CYear BeginDate EndDate ShipOut ReturnIn BeginMe EndMe
2001 1/1/2001 12/31/2001 3/13/2001 5/15/2005 3/13/2001 12/31/2001
2002 1/1/2002 12/31/2002 3/13/2001 5/15/2005 1/1/2002 12/31/2002
2003 1/1/2003 12/31/2003 3/13/2001 5/15/2005 1/1/2003 12/31/2003
2004 1/1/2004 12/31/2004 3/13/2001 5/15/2005 1/1/2004 12/31/2004
2005 1/1/2005 12/31/2005 3/13/2001 5/15/2005 1/1/2005 5/15/2005

The equipment removed 3/13/2001 and returned 5/15/2005 returns 5 rows, the
first and last being for partial years.

I believe this is what you want. If you have trouble adjusting this to
your
data, please fill in what that data is so I can make the adjustments.

Tom Ellison
Equipment Utilization
Ok, I need to figure out equipment utilization, the table I have is the
Click to expand...
[quoted text clipped - 3 lines]
you split it per year 6 months in 2005 and 6 months in 2006?
Any help would be greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Ok, I think I got it thanks!

Tom said:
Dear Russ:

Not very simply, but not too bad.

Let's create a table AnnualRange:

2005 1/1/2005 12/31/2005
2006 1/1/2006 12/31/2006

Put more years than just these in the table.

Is the "ship date" always less than the "receive date". That's the way I
understand this part.

A complete solution would also take usage out on 3/13/2002 and returning
3/13/2006 to have 5 parts:

3/13/2002 to 12/31/2002
1/1/2003 to 12/31/2003
1/1/2004 to 12/31/2004
1/1/2005 to 12/31/2005
1/1/2006 to 3/13/2006

I start with a cross product of the two tables, then eliminate those rows
not appropriate.

SELECT A.CYear, A.BeginDate, A.EndDate, B.ShipOut, B.ReturnIn
FROM AnnualRange A, EquipUtil B

The above reveals the tables I created to test this:

EquipUtil
ShipOut date/time
ReturnIn date/time

The AnnualRange table is as shown above.

The finished query:

SELECT A.CYear, A.BeginDate, A.EndDate, B.ShipOut, B.ReturnIn,
IIf(A.BeginDate < B.ShipOut, B.ShipOut, A.BeginDate) AS BeginMe,
IIf(A.EndDate > B.ReturnIn, B.ReturnIn, A.EndDate) AS EndMe
FROM AnnualRange A, EquipUtil B
WHERE A.BeginDate <= B.ReturnIn
AND A.EndDate >= B.ShipOut

You will need to adapt this to your table and column names.

Here's the test data and results:

AnnualRange CYear BeginDate EndDate
2000 1/1/2000 12/31/2000
2001 1/1/2001 12/31/2001
2002 1/1/2002 12/31/2002
2003 1/1/2003 12/31/2003
2004 1/1/2004 12/31/2004
2005 1/1/2005 12/31/2005
2006 1/1/2006 12/31/2006

EquipUtil ShipOut ReturnIn
3/13/2001 5/15/2005

MQry CYear BeginDate EndDate ShipOut ReturnIn BeginMe EndMe
2001 1/1/2001 12/31/2001 3/13/2001 5/15/2005 3/13/2001 12/31/2001
2002 1/1/2002 12/31/2002 3/13/2001 5/15/2005 1/1/2002 12/31/2002
2003 1/1/2003 12/31/2003 3/13/2001 5/15/2005 1/1/2003 12/31/2003
2004 1/1/2004 12/31/2004 3/13/2001 5/15/2005 1/1/2004 12/31/2004
2005 1/1/2005 12/31/2005 3/13/2001 5/15/2005 1/1/2005 5/15/2005

The equipment removed 3/13/2001 and returned 5/15/2005 returns 5 rows, the
first and last being for partial years.

I believe this is what you want. If you have trouble adjusting this to your
data, please fill in what that data is so I can make the adjustments.

Tom Ellison
Equipment Utilization
Ok, I need to figure out equipment utilization, the table I have is the
Click to expand...
[quoted text clipped - 3 lines]
you split it per year 6 months in 2005 and 6 months in 2006?
Any help would be greatly appreciated.
Click to expand...
Click to expand...
 
Tom,
Any idea how to capture a piece of equipment that happened not to ship out
one year, i.e. not ship date and no return date, or maybe the unit has never
shipped out what can you do then? Thanks for all your help!
Russ

Tom said:
Dear Russ:

You need two things:

- a table which I described

- a query like what I gave you

The only query is the SELECT query I sent. The entries in the table were
typed manually. They could also be created in an automated fashion.

In the results from my example, there's a lot more information than just
what you need. Maybe that's confusing. More simply:

MQry CYear BeginMe EndMe
2001 3/13/2001 12/31/2001
2002 1/1/2002 12/31/2002
2003 1/1/2003 12/31/2003
2004 1/1/2004 12/31/2004
2005 1/1/2005 5/15/2005

An item that went out 3/31/2001 and returned 5/15/2005 gives the results
shown.

Tom Ellison
Tom,
It looks like you understand what I want to do, but I am having trouble
Click to expand...
[quoted text clipped - 84 lines]
Click to expand...
 
Dear Russ:

You present two cases:

- no ship date and no return date

- unit has never shipped out

Are these two different cases? How?

Also, what do you want to show about such an item?

Are you talking about a unit that doesn't appear at all in the table I
called EquipUtil? Perhaps there's another table in which it does appear.
So, is it the case that you need to also show every item from this other
table? I suggest a LEFT JOIN from that table to the query I wrote.

Tom Ellison


rmgalasinski via AccessMonster.com said:
Tom,
Any idea how to capture a piece of equipment that happened not to ship out
one year, i.e. not ship date and no return date, or maybe the unit has
never
shipped out what can you do then? Thanks for all your help!
Russ

Tom said:
Dear Russ:

You need two things:

- a table which I described

- a query like what I gave you

The only query is the SELECT query I sent. The entries in the table were
typed manually. They could also be created in an automated fashion.

In the results from my example, there's a lot more information than just
what you need. Maybe that's confusing. More simply:

MQry CYear BeginMe EndMe
2001 3/13/2001 12/31/2001
2002 1/1/2002 12/31/2002
2003 1/1/2003 12/31/2003
2004 1/1/2004 12/31/2004
2005 1/1/2005 5/15/2005

An item that went out 3/31/2001 and returned 5/15/2005 gives the results
shown.

Tom Ellison
Tom,
It looks like you understand what I want to do, but I am having trouble
Click to expand...
[quoted text clipped - 84 lines]
you split it per year 6 months in 2005 and 6 months in 2006?
Any help would be greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 

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

Days Count 2
Date Range Comparison 3
SQL query urgent pls help 2
Math Question 6
Multiple Dates in a table 1
Query two subforms from 3 combo boxes 3
Need help with a complex data set 12
Delete Related records 2

Back
Top