4
SELECT ID, AppID, Description, Min([Transaction Date]) AS TransactionDate
FROM AppProsHist
WHERE [Description]='Non-Final Rejection'
GROUP BY ID, AppID, Description
ORDER BY TransactionDate DESC

I keep receiving a Parameter Value box for "TransactionDate." Does anyone know how I make that go away?

HansUp
  • 95,961
  • 11
  • 77
  • 135
Johnfun10
  • 118
  • 8
  • do you actually have a field called "Transaction Date" in your base table? (with a space) – koriander Feb 22 '16 at 19:24
  • yes I do, but The Min() isn't actually doing what I hoped it would do. However, when I take away that portion of the code everything breaks. – Johnfun10 Feb 22 '16 at 19:34

1 Answers1

2

TransactionDate is an alias for the field expression Min([Transaction Date])

Access does not accept an alias in the ORDER BY. Either of these 2 alternatives should work ...

ORDER BY Min([Transaction Date]) DESC
ORDER BY 4 DESC
HansUp
  • 95,961
  • 11
  • 77
  • 135
  • Thanks. Do you know how to make it so that only the first/earliest date is picked? I was under the impression that the Min() did that, although it doesn't seem to work. – Johnfun10 Feb 22 '16 at 19:39
  • `Min([Transaction Date])` should give you the first/earliest/oldest date for each combination of `ID`, `AppID`, and `Description`. (Actually, all the result set rows will have same `Description` because of the `WHERE` clause ... but I don't see why that matters here.) If that is *not* what you're seeing, please show us a data sample and what you get from the query based on that data sample. – HansUp Feb 22 '16 at 19:44
  • Or did you want the query to return only one row, with the earliest date which satisfies the `WHERE [Description]='Non-Final Rejection'` constraint? – HansUp Feb 22 '16 at 19:46
  • That sounds right, I just need the Transaction to come back with the earliest date. – Johnfun10 Feb 22 '16 at 20:01
  • so lets say the AppID is "98" and the ID's are 12,14,16, (different numbers) and there are three dates. The query is returning each date, in seemingly no order. I want just the earliest date and for the AppID of "98" – Johnfun10 Feb 22 '16 at 20:08
  • 1
    No, let's say the question is about making the "parameter value" issue go away (it is!) After I showed you how to make it go away, you discovered the query does not give you what you want. That is a new and different question; make it so. – HansUp Feb 22 '16 at 20:13