Setting a filter in the SELECT-statement

Is it possible to set a filter in the SELECT-statementpart
Normaly you the filter is set in the WHERE-statementpart, but that is too late for me in my statement. Right after the SELECTing the field I want to set a filter to the query Is it possible

My statement looks like this now:

I am confused as to what you are trying to do here. Why can't you just put this at the end of your query

WHERE [DatabaseName$Sales Price HAG].[Sales Code] = 'SALES_CODE'

Jarret

Jarret,

thx for your fast reply.

It's hard for me to explane why I want it, but I give it a try. Please ask, if I am not clear enough.....
If i am setting the filter in the WHERE-statement it is too late. The filter must be set before the MAX-statement, because the MAX-statement also set a kind of filter. Because of the MAX-statement my query is filtered on the startingdate and -time. So from every item I get only one result back. But if the item has more than one 'Sales Code' (for example 3), I want to see the items 3 times with the Sales code, but also with the most recent startingdate and -time.

I hope I explained my problem a little bit. Maybe I make a big mistake somewhere......

Would adding [Sales Code] to the subquery group solve your problem   Like this:

SELECT [DatabaseName$Item].No_,
 [DatabaseName$Item].Description,
 [DatabaseName$Sales Price HAG].[Unit Price],
 [DatabaseName$Sales Price HAG].[Starting Date],
 [DatabaseName$Sales Price HAG].[Starting Time],
 [DatabaseName$Sales Price HAG].[Sales Code]        (<== here on this item I want to set a filter)
FROM [DatabaseName$Item]
 INNER JOIN [DatabaseName$Sales Price HAG] ON [DatabaseName$Item].No_ = [DatabaseName$Sales Price HAG].[Item No_]
 INNER JOIN (
  SELECT [Item No_], [Sales Code], MAX([Starting Date]) AS MaxStartingDate, MAX([Starting Time]) AS MaxStartingTime
  FROM [DatabaseName$Sales Price HAG] AS [DatabaseName$Sales Price HAG_1]
  GROUP BY [Item No_], [Sales Code]
 ) AS SubQuery1 ON [DatabaseName$Sales Price HAG].[Item No_] = SubQuery1.[Item No_]
  AND [DatabaseName$Sales Price HAG].[Starting Date] = SubQuery1.MaxStartingDate
  AND [DatabaseName$Sales Price HAG].[Starting Time] = SubQuery1.MaxStartingTime
  AND [DatabaseName$Sales Price HAG].[Sales Code] = SubQuery1.[Sales Code]

This will give you the MAX startingdate & time for each [Item No_] + [Sales Code] combination.  So, if you have 3 sales codes for an item, you will get 3 records back from the subquery, each with their own MAX startingdate & time.

Hope this helps.

Jarret

Yep, thats the solution! Thx!!

I had tried some options but all the time I forgot the GROUP BY-statement..... :)