DLookUp or Other Option

  • Thread starter Thread starter pu5 via AccessMonster.com
  • Start date Start date
P

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?
 
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?
Click to expand...

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
 
Thanks for the response. I'm not sure I totally understand but am continuing
to review.

Again, thanks.


Michael said:
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)�
Click to expand...
[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?
Click to expand...

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
Click to expand...
 
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
Click to expand...
Click to expand...
 
Thanks for the response. I'm not sure I totally understand but am continuing
to review.

Again, thanks.


Michael said:
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)�
Click to expand...
[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?
Click to expand...

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
Click to expand...
Click to expand...
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.
 
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

Michael said:
Thanks for the response. I'm not sure I totally understand but am continuing
to review.
Click to expand...
[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.
Click to expand...
 
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

Michael said:
Thanks for the response. I'm not sure I totally understand but am continuing
to review.
Click to expand...
[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
Click to expand...

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.
Click to expand...
Click to expand...
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
 
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...
Click to expand...
[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
Click to expand...
 
Back
Top