Tricky query

[DaveP]

I have been trying to nut out a query for the last few days and I have
found a solution using a vba function but I would like to optimize it
so it runs a whole lot faster.

I am developing a database for a contracting company which records all
maintenance done on their vehicles. One of the things that is recorded
is engine hours (ie each machine has an hour meter).

Each day the machine operators record a log sheet of what they do
(including recording the hour meter reading) which is entered into the
database the next working day. If no one runs a machine there is no
log sheet and hence no data entry for it.

They want to report the number of hours a machine has operated for
between two dates. It sounds easy enough but there are a few business
rules that apply. If the machine wasn't operated on the start date (it
won't have an entry) then use the previous entry. The end date is easy
as it uses the the entry <= end date. If the machine doesn't have an
entry prior to the start date then we need to use the next date (if
there is one). Below is an excerpt from my Engine Hours Table.

EquipmentID EngHrsDate EngHrs
11 20/10/2006 24627
11 23/10/2006 24644
11 25/10/2006 24653
11 26/10/2006 24663
11 27/10/2006 24680
11 31/10/2006 24689
11 1/11/2006 24698
11 2/11/2006 24707
11 3/11/2006 24716
11 7/11/2006 24725
11 8/11/2006 24735
11 9/11/2006 24744
11 10/11/2006 24753
11 13/11/2006 24754

As you can see if I run a query from 23/10/2006 to 10/11/2006 it's
easy. If I choose 21/10/2006 to 3/11/2006 then I need to go back to
20/11/2006 for previous hours. If the query is 19/10/2006 to 3/11/2006
then the first record needs to be selected.

I want to be able to run this using a query only if possible, as
running a function over this slows it down no end (or is this my bad
programming). I won't post my code as yet but I will if need be.

If anyone could help or point me in the right direction then it would
be greatly appreciated.

TIA

David

BTW I am using Access 2002 SP2.

 

[Allen Browne]

Use a subquery to get the most recent date for this equipment on or before
the date you wish to start from.

The Criteria under the EngHrsDate would be something like this:
Between (SELECT Max(EngHrsDate) FROM EngineHours AS A
WHERE A.EquipmentID = EngineHours.EquipmentID
AND A.EngHrsDate <= #11/20/2006#) And #11/3/2006#

If there no previous date, the subquery will return Null. Use Nz() to supply
another value to use for Null.

Note that within the SQL statement, the dates must be in the American
format, whereas in the query design interface, they are interpreted as
Australian dates.

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

 

[DaveP]

Thanks for your help. Although it didn't give me exactly what I was
after you put me on the right track. What I was after was a min
EngHrsDate and max EngHrsDate with the associated Engine Hours. Pasted
below is the working query (for those that are interested). Please
post any comments if you have any better way of doing it.

SELECT Min_EH.EquipmentID, Nz((SELECT Min(A.EngHrsDate)
FROM tblEngineHours A WHERE A.EngHrsDate>=#11/1/2006# AND A.EquipmentID
= Min_EH.EquipmentID),(SELECT Max(A.EngHrsDate) FROM tblEngineHours A
WHERE A.EngHrsDate<=#11/1/2006# AND A.EquipmentID =
Min_EH.EquipmentID)) AS MinDate, Max(Max_EH.EngHrsDate) AS MaxDate,
Nz((SELECT Min(A.EngHrs) FROM tblEngineHours A WHERE
A.EngHrsDate>=#11/1/2006# AND A.EquipmentID =
Min_EH.EquipmentID),(SELECT Max(A.EngHrs) FROM tblEngineHours A WHERE
A.EngHrsDate<=#11/1/2006# AND A.EquipmentID = Min_EH.EquipmentID)) AS
MinHrs, Max(Max_EH.EngHrs) AS MaxHrs
FROM (tblEquipment AS EQ INNER JOIN tblEngineHours AS Max_EH ON
EQ.EquipmentID = Max_EH.EquipmentID) INNER JOIN tblEngineHours AS
Min_EH ON EQ.EquipmentID = Min_EH.EquipmentID
WHERE (((EQ.EquipmentActive)=Yes) AND
((Max_EH.EngHrsDate)<=#11/11/2006#) AND
([Max_EH].[EngHrsDate]<=#11/11/2006#))
GROUP BY Min_EH.EquipmentID;

I will be replacing the dates with references to form date ranges.

Once again Allen thanks for your help putting me on the straight and
narrow.

Dave.