Alternative to domain functions?

[BruceM]

I have several queries along the lines of the following:

SELECT tblInsp.InspID, tblInsp.I_EquipID, tblInsp.InspDate,
tblInsp.EquipMgr, DMax("InspDate","tblInsp","I_EquipID = " & [EquipID] & "
AND EquipMgr Is Not Null") AS LastInsp,
DateAdd([InspCycleInterval],[InspCycleNumber],[LastInsp]) AS NextInsp
FROM tblEquip INNER JOIN tblInsp ON tblEquip.EquipID = tblInsp.I_EquipID
ORDER BY tblInsp.InspDate;

This is a sort of summary view. There are about 8-10 other fields that I do
not think matter for purposes of this question. I hope I have not omitted
anything important.

tblInsp holds equipment inspection records. Equipment information is stored
in tblEquip. EquipID is the PK of tblEquip, and I_EquipID is the related
field in tblInsp. InspCycleInterval stores a text value such as "m" that
can be used in DateAdd, and InspCycleNumber is the integer for DateAdd.
Both are fields in tblEquip.

The question has to do with using DMax for finding the date of the most
recent inspection (LastInsp), and with using the LastInsp alias together
with DateAdd to find the date of the next inspection. It's safe to say
there could be 5,000 inspection records per year, and more are certainly
possible. I wonder if the DMax function will start to bog down. In any
case, I wonder if there is a better way of doing this, such as a subquery
that finds the Top 1 InspDate or something like that. Even if the DMax
function is the way to go, is there a problem with using the LastInsp alias
to find NextInsp.

 

[Jerry Whittle]

Check out Trevor Best's basLookup module:

http://www.mvps.org/access/modules/mdl0012.htm

 

[BruceM]

Thanks for the reply. The tLookup function is pretty cool in that it is
possible to look up a value in something other than CurrentDB, but from what
I have been able to find out it solved problems with linked tables lookups
being slow in Access 97, but does not offer a performance advantage over
DMax in later versions of Access (I am using 2003), so I think I will stick
with DMax unless I run into performance problems as the database accumulates
records. I wanted to try a subquery, but could not get very far with it.

SELECT tblInsp.InspID, tblInsp.I_EquipID, tblInsp.InspDate,
tblInsp.EquipMgr,
(SELECT Max(LD.InspDate)
FROM tblInsp AS LD
WHERE LD.I_EquipID = tblEquip.EquipID
AND LD.EquipMgr Is Not Null) AS LastInsp,
DateAdd([InspCycleInterval],[InspCycleNumber],[LastInsp]) AS NextInsp
FROM tblEquip
INNER JOIN tblInsp
ON tblEquip.EquipID = tblInsp.I_EquipID
ORDER BY tblInsp.InspDate;

The problem, I expect, is that the first part of the WHERE clause in the
subquery is supposed to say:
"WHERE LD.I_EquipID = {EquipID in the main query}, but does not. In VBA I
would assign tblEquip.EquipID as a string variable and concatenate it into
the expression, but I can't figure out how to accomplish that sort of thing
in SQL.

Check out Trevor Best's basLookup module:

http://www.mvps.org/access/modules/mdl0012.htm
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have several queries along the lines of the following:

SELECT tblInsp.InspID, tblInsp.I_EquipID, tblInsp.InspDate,
tblInsp.EquipMgr, DMax("InspDate","tblInsp","I_EquipID = " & [EquipID] &
"
AND EquipMgr Is Not Null") AS LastInsp,
DateAdd([InspCycleInterval],[InspCycleNumber],[LastInsp]) AS NextInsp
FROM tblEquip INNER JOIN tblInsp ON tblEquip.EquipID = tblInsp.I_EquipID
ORDER BY tblInsp.InspDate;

This is a sort of summary view. There are about 8-10 other fields that I
do
not think matter for purposes of this question. I hope I have not
omitted
anything important.

tblInsp holds equipment inspection records. Equipment information is
stored
in tblEquip. EquipID is the PK of tblEquip, and I_EquipID is the related
field in tblInsp. InspCycleInterval stores a text value such as "m" that
can be used in DateAdd, and InspCycleNumber is the integer for DateAdd.
Both are fields in tblEquip.

The question has to do with using DMax for finding the date of the most
recent inspection (LastInsp), and with using the LastInsp alias together
with DateAdd to find the date of the next inspection. It's safe to say
there could be 5,000 inspection records per year, and more are certainly
possible. I wonder if the DMax function will start to bog down. In any
case, I wonder if there is a better way of doing this, such as a subquery
that finds the Top 1 InspDate or something like that. Even if the DMax
function is the way to go, is there a problem with using the LastInsp
alias
to find NextInsp.