From: John on
I am trying to build a query that sorts data ascending order by date, so that
when I select "Last" in the Total row for other fields in the query I will
get the newest value (I have an account field that doesn't change but I want
to retrieve the newest account description which can change from one day to
the next so I want the desciption of the newest record). The problem is I
don't want the date column in the query since I am trying to also trying to
group and sum the data and don't want a new row of data for each date value.
Any ideas?
From: ghetto_banjo on
I would use Max instead of Last to get the most recent date. "Last"
is kind of a a strange group by function. Someone else could explain
it better than me.


After that, you can just Uncheck the "Show" checkbox and it will not
show that date field in the query results
From: KARL DEWEY on
Selecting "Last" in the Total row for other fields in the query will not
always get the newest value.
Use a sub query or query joined on the date and any other related field.

If you want more information post your SQL.

--
Build a little, test a little.


"John" wrote:

> I am trying to build a query that sorts data ascending order by date, so that
> when I select "Last" in the Total row for other fields in the query I will
> get the newest value (I have an account field that doesn't change but I want
> to retrieve the newest account description which can change from one day to
> the next so I want the desciption of the newest record). The problem is I
> don't want the date column in the query since I am trying to also trying to
> group and sum the data and don't want a new row of data for each date value.
> Any ideas?
From: John W. Vinson on
On Wed, 10 Mar 2010 11:01:01 -0800, John <John(a)discussions.microsoft.com>
wrote:

>I am trying to build a query that sorts data ascending order by date, so that
>when I select "Last" in the Total row for other fields in the query I will
>get the newest value (I have an account field that doesn't change but I want
>to retrieve the newest account description which can change from one day to
>the next so I want the desciption of the newest record). The problem is I
>don't want the date column in the query since I am trying to also trying to
>group and sum the data and don't want a new row of data for each date value.
>Any ideas?

LAST is a rather irksome and all but useless group operator. It returns the
last record... *in disk storage order*, an order which you cannot control and
has no firm relationship to the values in the record!

Use a Subquery, using =(SELECT Max([datefield]) FROM mytable WHERE
<appropriate criteria>)

as a criterion on the date field.
--

John W. Vinson [MVP]