using "Last" in query desgin
in [Queries]
|
Prev: How to populate a column in a row based on a value in another colu
Next: Data Not Displayed in Query
From: John on 10 Mar 2010 14:01 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 10 Mar 2010 14:13 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 10 Mar 2010 14:51 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 10 Mar 2010 17:00
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] |