Date Difference Report

[pu5]

I have a query (qryShear) that selects records from a table (tblLbrHrs).
Example follows...

EqNoDesc EqNo Desc CompDate
Shear_A 1250 desc.detail 08/27/05
Shear_A 1250 desc.detail 09/09/05
Shear_C 1475 desc.detail 05/13/05
Shear_B 1800 desc.detail 03/05/05
Shear_A 1250 desc.detail 05/27/05
Shear_B 1800 desc.detail 03/25/05
Shear_C 1475 desc.detail 05/24/05

I want to run a report that produces the following results...

Equipment Desc Detail Compl Date Days Between Repairs
1250 - Shear_A desc.detail 05/27/05 0 days
desc.detail 08/27/05 92
days
desc.detail 09/09/05 13
days

1800 - Shear_B desc.detail 03/15/05 0 days
desc.detail 03/25/05 20
days

1475 - Shear_C desc.detail 05/13/05 0 days
desc.detail 05/24/05 11
days

My problem is how do I calculate the Days Between Repairs?

 

[yanto]

If you don't know the formula then where does these numbers (92, 13,
20 and 11) come from?

 

[pu5 via AccessMonster.com]

This was merely an example. I entered information in Excel and calculated
the date difference; however, I plan on maintaining this information in
Access.

 

[John Spencer]

You should be able to use a Subquery in the SELECT clause to get the
prior date. UNTESTED SQL would look something like the following.

SELECT EqNoDesc
, EqNo
, Repairs.Desc
, CompDate
, DateDiff("d",
NZ((SELECT Max(T.CompDate)
FROM Repairs as T
WHERE T.CompDate< Repairs.CompDate
AND T.EqNo = Repairs.EqNo),Repairs.CompDate)
Repairs.CompDate) as ElapsedDays
FROM Repairs

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[pu5 via AccessMonster.com]

Thanks very much for the information and assistance. Using the fields from
my table/query I modified to the following; however, when I run the query I
am receiving a "Syntax Error". I have reviewed and reviewed but can't find
the issue. Can you assist?

Again, thanks.


Expr1: (SELECT EqNoDesc
, EqNo
, Desc
, CmpDt
, DateDiff("d",
NZ((SELECT Max(T.CmpDt)
FROM tblLbrHrs as T
WHERE T.CmpDt< tblLbrHrs.CmpDt
AND T.EqNo = tblLbrHrs.EqNo), tblLbrHrs.CompDate)
tblLbrHrs.CompDate) as ElapsedDays
FROM tblLbrHrs)

You should be able to use a Subquery in the SELECT clause to get the
prior date. UNTESTED SQL would look something like the following.

SELECT EqNoDesc
, EqNo
, Repairs.Desc
, CompDate
, DateDiff("d",
NZ((SELECT Max(T.CompDate)
FROM Repairs as T
WHERE T.CompDate< Repairs.CompDate
AND T.EqNo = Repairs.EqNo),Repairs.CompDate)
Repairs.CompDate) as ElapsedDays
FROM Repairs

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I have a query (qryShear) that selects records from a table (tblLbrHrs).
Example follows...

[quoted text clipped - 26 lines]

My problem is how do I calculate the Days Between Repairs?

 

[John Spencer]

A subquery in the from clause can only return ONE value. You seem to be
asking it to return 5 values.

I think you might want just the following.

Field: Expr1: DateDiff("d",
NZ((SELECT Max(T.CmpDt)
FROM tblLbrHrs as T
WHERE T.CmpDt< tblLbrHrs.CmpDt
AND T.EqNo = tblLbrHrs.EqNo), tblLbrHrs.CompDate)
tblLbrHrs.CompDate)

What I posted would have been the entire query (Select View: SQL from the
menu when you are in design view)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thanks very much for the information and assistance. Using the fields
from
my table/query I modified to the following; however, when I run the query
I
am receiving a "Syntax Error". I have reviewed and reviewed but can't
find
the issue. Can you assist?

Again, thanks.


Expr1: (SELECT EqNoDesc
, EqNo
, Desc
, CmpDt
, DateDiff("d",
NZ((SELECT Max(T.CmpDt)
FROM tblLbrHrs as T
WHERE T.CmpDt< tblLbrHrs.CmpDt
AND T.EqNo = tblLbrHrs.EqNo), tblLbrHrs.CompDate)
tblLbrHrs.CompDate) as ElapsedDays
FROM tblLbrHrs)

You should be able to use a Subquery in the SELECT clause to get the
prior date. UNTESTED SQL would look something like the following.

SELECT EqNoDesc
, EqNo
, Repairs.Desc
, CompDate
, DateDiff("d",
NZ((SELECT Max(T.CompDate)
FROM Repairs as T
WHERE T.CompDate< Repairs.CompDate
AND T.EqNo = Repairs.EqNo),Repairs.CompDate)
Repairs.CompDate) as ElapsedDays
FROM Repairs

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I have a query (qryShear) that selects records from a table (tblLbrHrs).
Example follows...

[quoted text clipped - 26 lines]

My problem is how do I calculate the Days Between Repairs?

 

[pu5 via AccessMonster.com]

Obviously I am new to Subqueries; so please bear with me. Following is the
SQL view from my existing query.

SELECT tblLbrHrs.EqNoDesc, tblLbrHrs.EqNo, tblLbrHrs.Desc, tblLbrHrs.CmpDt
FROM tblLbrHrs
WHERE (((tblLbrHrs.EqNoDesc) Like "*Shear*") AND ((tblLbrHrs.Desc) Like
"*Flipped*"));

Can I create another column in thge "Grid View" adding the information you
provided below?

A subquery in the from clause can only return ONE value. You seem to be
asking it to return 5 values.

I think you might want just the following.

Field: Expr1: DateDiff("d",
NZ((SELECT Max(T.CmpDt)
FROM tblLbrHrs as T
WHERE T.CmpDt< tblLbrHrs.CmpDt
AND T.EqNo = tblLbrHrs.EqNo), tblLbrHrs.CompDate)
tblLbrHrs.CompDate)

What I posted would have been the entire query (Select View: SQL from the
menu when you are in design view)

Thanks very much for the information and assistance. Using the fields
from

[quoted text clipped - 45 lines]

 

[John Spencer]

Yes, I can't guarantee it will work. But you should be able to enter the
expression into field "cell" as I posted it. Starting with the "DateDiff".


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Obviously I am new to Subqueries; so please bear with me. Following is
the
SQL view from my existing query.

SELECT tblLbrHrs.EqNoDesc, tblLbrHrs.EqNo, tblLbrHrs.Desc, tblLbrHrs.CmpDt
FROM tblLbrHrs
WHERE (((tblLbrHrs.EqNoDesc) Like "*Shear*") AND ((tblLbrHrs.Desc) Like
"*Flipped*"));

Can I create another column in thge "Grid View" adding the information you
provided below?

A subquery in the from clause can only return ONE value. You seem to be
asking it to return 5 values.

I think you might want just the following.

Field: Expr1: DateDiff("d",
NZ((SELECT Max(T.CmpDt)
FROM tblLbrHrs as T
WHERE T.CmpDt< tblLbrHrs.CmpDt
AND T.EqNo = tblLbrHrs.EqNo), tblLbrHrs.CompDate)
tblLbrHrs.CompDate)

What I posted would have been the entire query (Select View: SQL from the
menu when you are in design view)

Thanks very much for the information and assistance. Using the fields
from

[quoted text clipped - 45 lines]

My problem is how do I calculate the Days Between Repairs?

 

[pu5 via AccessMonster.com]

Thanks...after some tweaking it appears to work. I have one final question
related to this query. Following is a selection from the SQL view...

WHERE (((tblLbrHrs.EqNoDesc) Like "*Shear*") AND ((tblLbrHrs.Desc) Like
"*Flipped*")) OR (((tblLbrHrs.Desc) Like "*changed*"));

The results I am receiving are not confined to EqNoDesc Like *Shear* and...
It appears I am getting EqNoDesc Like *Shear* or Desc Like *Flipped* or Desc
Like *Changed*. Any insight on this issue?

Yes, I can't guarantee it will work. But you should be able to enter the
expression into field "cell" as I posted it. Starting with the "DateDiff".

Obviously I am new to Subqueries; so please bear with me. Following is
the

[quoted text clipped - 28 lines]