B
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.
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.