HI dirk
yes i did get the query working (i had reposted) and i have realised
the same thing was required for this question,
i managed to sort it out so sorry for inconveniance, what i would like
to try and understand is what access is doing in these sorts of
situations it would be a reasonable assumption that you select the
latest date for each record and it should return the latest price,
Well, that's true, but you have to take into account the way that SQL
works. In a totals query, i.e. one that uses the GROUP BY clause, SQL
allows you only to select two types of fields: those you are grouping
by, and those that are the result of aggregate expressions (like Sum(),
Max(), Min(), etc.) that operate at the group level. You want to
extract data from the field [Close], so that means that in a *single*
SELECT statement you have to either group by that field or return, say,
Max([Close]). But if you group by
Code:
and [Close], you're going to
get a separate record back for each combination of [Code] and [Close],
and that's not what you want, while if you take Max([Close]) for each
[Code] it isn't necessarily (or even likely) the case that the highest
value of [Close] for each [Code] is the latest one.
So you need a subquery of some sort to identify the latest date for each
[Code], and then you can use that in the main query to filter the
results. There are several ways to go about this, as you saw if you
read that web page I pointed you to last time. One would be to embody
the subquery as what I think they call a "derived table", and then join
on it. That's the approach I suggested last time. If we follow that
same approach this time, we can first define a SELECT statement that
gets the latest date for each [Code], like this:
SELECT
[Code],
Max([Date]) As LastDate
FROM
[Daily-Stock-Prices]
GROUP BY
[Code]
That will give us one record per [Code], containing the [Code] and the
latest date in the table for that [Code]. Then, since as I understand
it there's only one record per [Code] per day, we can join the results
of that SELECT statement back to the table to get the records we want:
SELECT
P.Code,
L.LastDate,
P.Close
FROM
[Daily-Stock-Prices] As P
INNER JOIN
(SELECT
[Code],
Max([Date]) As LastDate
FROM
[Daily-Stock-Prices]
GROUP BY
[Code]
) As L
ON P.Code = L.Code;
That's "air SQL" but something like that ought to work.
[QUOTE]
I am in the middle of exams at present and havent a lot of time to nut
this one out properly, but i take on board about my naming conventions
and avoid these words[/QUOTE]
That's will make your life easier in the future. Good luck
[QUOTE]
As a question would this be quicker in recordsets with ADO?, or even
possible.[/QUOTE]
It would be possible, using a couple of recordsets based on the
different SELECT statements, but it would be painfully slow. Pretty
much anything that can be done using straight SQL is *much* faster done
that way than using recordsets. You tell the query engine *what* you
want, and it figures out the best way to give it to you.
[/QUOTE]
