Help selecting an additional field in my query

[Guest]

So far I the following aggreate query. In addition to what is below I have a
date field for which should select the date that corresponds to the record
with the
Min(tbl_ASX_Data.Close). The field is called IMPORTDATE and is in the same
table.

Any help appreciated.

Bruce

SELECT tbl_ASX_Data.ASXCode, Min(tbl_ASX_Data.Close) AS MinOfClose
FROM tbl_ASX_Data
GROUP BY tbl_ASX_Data.ASXCode;

 

[John Spencer]

One method - using a coordinated sub-query in the where clause.

SELECT tbl_ASX_Data.ASXCode, tbl_ASX_Data.Close, ImportDate
FROM tbl_ASX_Data
WHERE Close = (
SELECT Min(Temp.Close) AS MinOfClose
FROM tbl_ASX_Data as Temp
WHERE Temp.AsxCode = tbl_ASX_Data.ASXCode)

 

[Guest]

Thanks John. This works. I have 2.2 million records in my table tbl_ASX_Data
so running is quite slow. Is there a more efficient wany to run E.g. would a
DLOOKUP be better in this case?

Bruce

 

[John Spencer]

DLookup will probably be slower, probably much slower.

However, you may be able to use two queries or use a sub-query in the join
clause

Two query approach--
Save the following query as qMinClose

SELECT ASXCode, Min(Close) as MinOfClose
FROM Tbl_AsxCode
GROUP BY ASXCode

Now use that in a second query

SELECT T.ASXCode, T.Close, T.ImportDate
FROM tbl_ASX_Data as T INNER JOIN qMinClose as Q
ON T.ASXCode = Q.AsXCode AND T.Close = Q.MinOfClose

Doing that all in one query using a subquery in the Join Clause

SELECT T.ASXCode, T.Close, T.ImportDate
FROM tbl_ASX_Data as T INNER JOIN
(SELECT ASXCode, Min(Close) as MinOfClose
FROM Tbl_AsxCode
GROUP BY ASXCode) as Q
ON T.ASXCode = Q.AsXCode AND T.Close = Q.MinOfClose

Access may reformat the above to

SELECT T.ASXCode, T.Close, T.ImportDate
FROM tbl_ASX_Data as T INNER JOIN
[SELECT ASXCode, Min(Close) as MinOfClose
FROM Tbl_AsxCode
GROUP BY ASXCode]. as Q
ON T.ASXCode = Q.AsXCode AND T.Close = Q.MinOfClose