Re: DLookUp or Other Option

In article <703f6a26f0483@uwe>, u33069@uwe says...
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
.

Relevant Pages

  • Re: Creating a report from a table with most info blank
    ... You should have an Equipment table (which lists all equipment ... It sounds as if you can base your report on a query that filters out ... into service then you can use your query to calculate the time it was down. ... Once the equipment repair is completed, the rest of the information is ...
    (microsoft.public.access.reports)
  • Creating a report from a table with most info blank
    ... I need to create a report from a tablethat is based on the base ... ID, Equipment, requestor, repairer, date equipment ... Once the equipment repair is completed, the rest of the information is added ... to the log through the Malfunction Report Log Entry Form I have already ...
    (microsoft.public.access.reports)
  • Re: Additional lines on report
    ... Inner join this to the qty returned field using <= ... equipment and there is more than one being returned I need the report ... return quantity so the user can write in each equipment number. ...
    (microsoft.public.vb.crystal)
  • Re: BT Wayleaves
    ... I saw a report in the paper recently where a company had used a false address ... but the electricity companies attitude to why they ... There is always the possibility this is stolen equipment but seems to me to be a mistaken purchase as unsuitable for the jobs they had in mind. ... If the equipment *is* stolen and police enquiries lead to your property, it may be much more difficult for you to avoid prosecution and the questioning will not be so friendly. ...
    (uk.business.agriculture)
  • RE: Query wont report
    ... I went back to begining queries and concatonated and [sanitation ... Jerry Whittle, Microsoft Access MVP ... I also have equipment table and department table linked from ... Now I want to generate a report grouped by location that identifies tasks ...
    (microsoft.public.access.gettingstarted)