DLookUp or Other Option

[pu5 via AccessMonster.com]

I am currently generating a set of records from a table using a query. These
are grouped by equipment number in a report (example below)…

Equipment: 1250
02/14/05 repair detail…
04/13/05 repair detail…
05/25/05 repair detail…
Equipment: 1250-1
07/22/05 repair detail…
09/13/05 repair detail…
10/10/05 repair detail…

What I am trying to do is add a field that calculates the difference between
the dates by group (example below)…

Equipment: 1250
02/14/05 repair detail… 0 days
04/13/05 repair detail… 27 days
05/25/05 repair detail… 38 days
Equipment: 1250-1
07/22/05 repair detail… 0 days
09/13/05 repair detail… 53 days
10/10/05 repair detail… 27 days

I have tried to use DLookUp to identify previous record dates and then
subtract but have been unsuccessful. Does anyone have any ideas?

 

[Michael Gramelspacher]

I am currently generating a set of records from a table using a query. These
are grouped by equipment number in a report (example below)�

Equipment: 1250
02/14/05 repair detail�
04/13/05 repair detail�
05/25/05 repair detail�
Equipment: 1250-1
07/22/05 repair detail�
09/13/05 repair detail�
10/10/05 repair detail�

What I am trying to do is add a field that calculates the difference between
the dates by group (example below)�

Equipment: 1250
02/14/05 repair detail� 0 days
04/13/05 repair detail� 27 days
05/25/05 repair detail� 38 days
Equipment: 1250-1
07/22/05 repair detail� 0 days
09/13/05 repair detail� 53 days
10/10/05 repair detail� 27 days

I have tried to use DLookUp to identify previous record dates and then
subtract but have been unsuccessful. Does anyone have any ideas?

OK, this is not exactly the same, but is a similiar example:
(watch line wrapping)


SELECT EquipmentRepairs.equipment_nbr,
Equipment.equipment_name,
EquipmentRepairs.repair_date,
EquipmentRepairs.repair_code,
EquipmentRepairs.repair_details,
NZ(DATEDIFF("d",(SELECT MAX(a.repair_date)
FROM EquipmentRepairs AS a
WHERE a.equipment_nbr =
EquipmentRepairs.equipment_nbr
AND a.repair_date <
EquipmentRepairs.repair_date),
EquipmentRepairs.repair_date),0) AS [Days
Since Last Repair]
FROM RepairCodes
INNER JOIN (Equipment
INNER JOIN EquipmentRepairs
ON Equipment.equipment_nbr =
EquipmentRepairs.equipment_nbr)
ON RepairCodes.repair_code =
EquipmentRepairs.repair_code;


equipment_nbr equipment_name repair_date repair_code
repair_details Days Since Last Repair
1250 Widget 2/14/2005 RO routine 0
1250 Widget 4/13/2005 RO routine 58
1250 Widget 5/25/2005 ADJ adjustment 42
1250-1 Super-Widget 7/22/2005 RO routine 0
1250-1 Super-Widget 9/13/2005 RO routine 53
1250-1 Super-Widget 10/10/2005 ADJ adjustment 27

 

[pu5 via AccessMonster.com]

Thanks for the response. I'm not sure I totally understand but am continuing
to review.

Again, thanks.

I am currently generating a set of records from a table using a query. These
are grouped by equipment number in a report (example below)�

[quoted text clipped - 22 lines]

I have tried to use DLookUp to identify previous record dates and then
subtract but have been unsuccessful. Does anyone have any ideas?

OK, this is not exactly the same, but is a similiar example:
(watch line wrapping)

SELECT EquipmentRepairs.equipment_nbr,
Equipment.equipment_name,
EquipmentRepairs.repair_date,
EquipmentRepairs.repair_code,
EquipmentRepairs.repair_details,
NZ(DATEDIFF("d",(SELECT MAX(a.repair_date)
FROM EquipmentRepairs AS a
WHERE a.equipment_nbr =
EquipmentRepairs.equipment_nbr
AND a.repair_date <
EquipmentRepairs.repair_date),
EquipmentRepairs.repair_date),0) AS [Days
Since Last Repair]
FROM RepairCodes
INNER JOIN (Equipment
INNER JOIN EquipmentRepairs
ON Equipment.equipment_nbr =
EquipmentRepairs.equipment_nbr)
ON RepairCodes.repair_code =
EquipmentRepairs.repair_code;

equipment_nbr equipment_name repair_date repair_code
repair_details Days Since Last Repair
1250 Widget 2/14/2005 RO routine 0
1250 Widget 4/13/2005 RO routine 58
1250 Widget 5/25/2005 ADJ adjustment 42
1250-1 Super-Widget 7/22/2005 RO routine 0
1250-1 Super-Widget 9/13/2005 RO routine 53
1250-1 Super-Widget 10/10/2005 ADJ adjustment 27

 

[PhilT]

pu5,

I am done this before, and it works for me. Go to microsoft knowledgebase ID
208953. They have a similar example for you to follow. Good luck. It much
easier.

Thanks for the response. I'm not sure I totally understand but am continuing
to review.

Again, thanks.
[quoted text clipped - 34 lines]

1250-1 Super-Widget 9/13/2005 RO routine 53
1250-1 Super-Widget 10/10/2005 ADJ adjustment 27

 

[Michael Gramelspacher]

Thanks for the response. I'm not sure I totally understand but am continuing
to review.

Again, thanks.

I am currently generating a set of records from a table using a query.These
are grouped by equipment number in a report (example below)�

[quoted text clipped - 22 lines]

I have tried to use DLookUp to identify previous record dates and then
subtract but have been unsuccessful. Does anyone have any ideas?

OK, this is not exactly the same, but is a similiar example:
(watch line wrapping)

SELECT EquipmentRepairs.equipment_nbr,
Equipment.equipment_name,
EquipmentRepairs.repair_date,
EquipmentRepairs.repair_code,
EquipmentRepairs.repair_details,
NZ(DATEDIFF("d",(SELECT MAX(a.repair_date)
FROM EquipmentRepairs AS a
WHERE a.equipment_nbr =
EquipmentRepairs.equipment_nbr
AND a.repair_date <
EquipmentRepairs.repair_date),
EquipmentRepairs.repair_date),0) AS [Days
Since Last Repair]
FROM RepairCodes
INNER JOIN (Equipment
INNER JOIN EquipmentRepairs
ON Equipment.equipment_nbr =
EquipmentRepairs.equipment_nbr)
ON RepairCodes.repair_code =
EquipmentRepairs.repair_code;

equipment_nbr equipment_name repair_date repair_code
repair_details Days Since Last Repair
1250 Widget 2/14/2005 RO routine 0
1250 Widget 4/13/2005 RO routine 58
1250 Widget 5/25/2005 ADJ adjustment 42
1250-1 Super-Widget 7/22/2005 RO routine 0
1250-1 Super-Widget 9/13/2005 RO routine 53
1250-1 Super-Widget 10/10/2005 ADJ adjustment 27

THe DATEDIFF Function gets the difference in days between (the
greatest (MAX) repair_date that is less than the current row
repair_date), and the current row repair_date.

The greatest (MAX) repair_date that is less than the current
row is found by using a subquery that returns a single date
value. What this amounts to is finding the difference in days
between two dates using the DATEDIFF function. So what that a
subquery was needed to get the one date.

If you already have a query that gives you everything except
the days difference column, then you only need to add a
computed column like here, but using your real column names and
table name. The table will have an alias as here, because it
references to the outer table.

 

[pu5 via AccessMonster.com]

I am not at all familiar with Subqueries. Can you "spoon feed" me? My table
is (tblLbrHrs). My fields are as follows...

EqNo (Equipment Number)
EqNoDesc (Equipment Number Description)
CmpDt (Complete Date)
Desc (Repair Description)

Also, can you expalin the need for the Joins and Inner Joins?

Sorry for the ignorance in this area.

Thanks

Thanks for the response. I'm not sure I totally understand but am continuing
to review.

[quoted text clipped - 39 lines]
THe DATEDIFF Function gets the difference in days between (the
greatest (MAX) repair_date that is less than the current row
repair_date), and the current row repair_date.

The greatest (MAX) repair_date that is less than the current
row is found by using a subquery that returns a single date
value. What this amounts to is finding the difference in days
between two dates using the DATEDIFF function. So what that a
subquery was needed to get the one date.

If you already have a query that gives you everything except
the days difference column, then you only need to add a
computed column like here, but using your real column names and
table name. The table will have an alias as here, because it
references to the outer table.

 

[Michael Gramelspacher]

I am not at all familiar with Subqueries. Can you "spoon feed" me? My table
is (tblLbrHrs). My fields are as follows...

EqNo (Equipment Number)
EqNoDesc (Equipment Number Description)
CmpDt (Complete Date)
Desc (Repair Description)

Also, can you expalin the need for the Joins and Inner Joins?

Sorry for the ignorance in this area.

Thanks

Thanks for the response. I'm not sure I totally understand but am continuing
to review.

[quoted text clipped - 39 lines]

1250-1 Super-Widget 9/13/2005 RO routine 53
1250-1 Super-Widget 10/10/2005 ADJ adjustment 27

THe DATEDIFF Function gets the difference in days between (the
greatest (MAX) repair_date that is less than the current row
repair_date), and the current row repair_date.

The greatest (MAX) repair_date that is less than the current
row is found by using a subquery that returns a single date
value. What this amounts to is finding the difference in days
between two dates using the DATEDIFF function. So what that a
subquery was needed to get the one date.

If you already have a query that gives you everything except
the days difference column, then you only need to add a
computed column like here, but using your real column names and
table name. The table will have an alias as here, because it
references to the outer table.

Your original post said you already had a query, so now add
this subquery as one more column to your query in design view
qrid.

Days Since Last Repair: NZ(DATEDIFF("d",(SELECT MAX(a.CmpDt)
FROM tblLbrHrs AS a WHERE a.EqNo = tblLbrHrs.EqNo
AND a.CmpDt < tblLbrHrs.CmpDt),tblLbrHrs.CmpDt),0)


At what pont were you going to become familiar with subqueries?
Might that time be now? What about joins?

I will suggest three books:

Database Design for Mere Mortals by Michael J. Hernandez
SQL Queries for Mere Mortals by Michael J. Hernandez and John
L. Viescas
SQL Programming Style by Joe Celko

 

[pu5 via AccessMonster.com]

Thanks for all the information. I was very close; however, at this point I'm
receiving a "Compile Error". I did notice that brackets were placed around
the tblLbrHrs.CmpDt at the end of the subquery (after I received the Compile
Error)...looked like the following...

[tblLbrHrs].[CmpDt]

What does this indicate?

Again, thanks for the assistance and recommended reading. I will follow up

I am not at all familiar with Subqueries. Can you "spoon feed" me? My table
is (tblLbrHrs). My fields are as follows...

[quoted text clipped - 31 lines]
Your original post said you already had a query, so now add
this subquery as one more column to your query in design view
qrid.

Days Since Last Repair: NZ(DATEDIFF("d",(SELECT MAX(a.CmpDt)
FROM tblLbrHrs AS a WHERE a.EqNo = tblLbrHrs.EqNo
AND a.CmpDt < tblLbrHrs.CmpDt),tblLbrHrs.CmpDt),0)

At what pont were you going to become familiar with subqueries?
Might that time be now? What about joins?

I will suggest three books:

Database Design for Mere Mortals by Michael J. Hernandez
SQL Queries for Mere Mortals by Michael J. Hernandez and John
L. Viescas
SQL Programming Style by Joe Celko