No, FIRST (and Last) both return a more or less random record within the
specified group. Min returns the smallest value within the group and
Max returns the largest value with the group.
Quoting from Access Help
The First and Last functions are analogous to the MoveFirst and MoveLast
methods of a DAO Recordset object. They simply return the value of a
specified field in the first or last record, respectively, of the result
set returned by a query. Because records are usually returned in no
particular order (unless the query includes an ORDER BY clause), the
records returned by these functions will be arbitrary.
My personal experience, is that the ORDER BY clause often does not seem
to impact the value actually returned.
John,
Thanks a million! Both are working correctly.
Regarding your kind comments on the qryFirstDeliveryDate query, I use
First together with sort key in ascending order should be equal to Min,
isn't it? However, you may be right that Min is quicker at least.
Ray
Yes, DMin is always going to be slower. Since it gets continually
called for each row in your query.
Your qryFirstDeliveryDate query is using FIRST which does not mean
EARLIEST date. It more or less means the first record in the group
that is retreived.
You might try using a coordinated subquery in your query to get your
DeliveryDate. I'm guessing here, but you might try the following.
Note that I used the NZ functions in place of your IIFs. You can use
you
SELECT tblProduct.ProCat,
tblProduct.Supplier,
tblProduct.Brand,
tblProduct.Model,
tblProduct.SuppliersModel,
tblProduct.Description,
tblProduct.Product_AW,
tblProduct.TE, tblProduct.GB, tblProduct.IB,
(SELECT Min(IIf([2nd Confirm date] Is Not Null,[2nd Confirm
date],
IIf([1st Confirm date] Is Not Null,[1st
Confirm date],
[ODATE])))
FROM [Shipping Data] as SD
WHERE SD.Model = tblProduct.Model) -4 as [Latest Insp Date],
tblProduct.Rmk
FROM tblProduct
WHERE tblProduct.By="John William"
ORDER BY tblProduct.ProCat,
tblProduct.Supplier,
tblProduct.Brand,
tblProduct.Model;
I am making a bunch of guesses about the structure of your tables.
If you have to use the old qryFirstDeliveryDate you might be able to
increase the speed by dropping the Order By clause
SELECT [Shipping data].Model,
Min(IIf([2nd Confirm date] Is Not Null,[2nd Confirm date],
IIf([1st Confirm date] Is Not Null,[1st Confirm date],
[ODATE]))) AS DeliveryDate
FROM [Shipping data]
GROUP BY [Shipping data].Model
John,
Excellent guess!! It works using DMin function and now becomes a
updatable recordset. However, it seems DMin function is slower - it
shows the data line by line vs popping up at the same time. Is it
its characteristics?
qryFirstDeliveryDate is as below:-
SELECT [Shipping data].Supplier, [Shipping data].Model,
First(IIf([2nd Confirm date] Is Not Null,[2nd Confirm date],IIf([1st
Confirm date] Is Not Null,[1st Confirm date],[ODATE]))) AS
DeliveryDate
FROM [Shipping data]
GROUP BY [Shipping data].Supplier, [Shipping data].Model
ORDER BY [Shipping data].Supplier, [Shipping data].Model,
First(IIf([2nd Confirm date] Is Not Null,[2nd Confirm date],IIf([1st
Confirm date] Is Not Null,[1st Confirm date],[ODATE])));
Any suggestion on improvement?
Many thanks,
Ray
I suspect the problem is caused by qryFirstDeliveryDate. I am
guessing that it finds the oldest date for a delivery. I suggest you
also post the sql for qryFirstDeliveryDate.
You may be able to solve your problem by using the DMin function and
dropping the qryFirstDeliveryDate completely. Perhaps the following
MAY work for you.
SELECT tblProduct.ProCat, tblProduct.Supplier,
tblProduct.Brand,
tblProduct.Model,
tblProduct.SuppliersModel,
tblProduct.Description,
tblProduct.Product_AW,
tblProduct.TE, tblProduct.GB, tblProduct.IB,
DMin("DeliveryDate","qryFirstDeliveryDate","Model=""" & Model &
"""") -4 as [Latest Insp Date],
tblProduct.Rmk
FROM tblProduct
WHERE (((tblProduct.By)="John William"))
ORDER BY tblProduct.ProCat,
tblProduct.Supplier,
tblProduct.Brand,
tblProduct.Model;
I have the following query and it works correctly to my requirement
except non updatable recordset. Can someone advise me any way to
modify it to updatable recordset.
SELECT tblProduct.ProCat, tblProduct.Supplier, tblProduct.Brand,
tblProduct.Model, tblProduct.SuppliersModel,
tblProduct.Description, tblProduct.Product_AW, tblProduct.TE,
tblProduct.GB, tblProduct.IB, [DeliveryDate]-4 AS [Latest Insp
Date], tblProduct.Rmk
FROM tblProduct LEFT JOIN qryFirstDeliveryDate ON tblProduct.Model
= qryFirstDeliveryDate.Model
WHERE (((tblProduct.By)="John William"))
ORDER BY tblProduct.ProCat, tblProduct.Supplier, tblProduct.Brand,
tblProduct.Model;
Thanks,
Ray
