The WHERE clause dilemma

[sougata666]

I have a list of employees with their category promotion dates (A category is better than B and so on. NA implies that the person is not yet categorised)

ID Category Date
-- ---------- -----
1 D 1/3/12
2 C 1/2/86
1 C 2/4/13
2 NA 1/3/81
3 NA 1/5/76
2 B 1/7/91

SELECT table.ID, Max(table.Date)
FROM table
WHERE (((table.Category)<>"NA"))
GROUP BY table.ID;

With the above code, I have managed to get only employee IDs who do not have NA in their category along with their last category upgradation date, like this:

ID Date
-- -----
1 2/4/13
2 1/7/91

But, as I am using the WHERE clause, I am being stopped from displaying the Category field. I want the Category field to be displayed as well, like so:

ID Category Date
-- --------- -----
1 C 2/4/13
2 B 1/7/91

How do I make this happen in Access 2013?

 

[jdraw]

What does ID represent? If it is a PK,you can't have more than 1 per table.
Tell us in plain English what this table represents in simple English.

 

[arnelgp]

you can't with simple query/aggregate query.
refer to your last post http://www.access-programmers.co.uk/forums/showthread.php?t=286412

 

[sougata666]

ID is not the Primary Key. I wrote ID simply to represent an employee. Take it as as employee code. The code is working in Access. I just need to display the category alongside as well.

 

[plog]

Let me put it in plain English for you:

You want to see the record with the most recent [Date] date for each ID that isn't category NA.

Correct? If so, you need to take the existing query you posted and build another query upon it and table, then you can get what you want. You link the MAX(Date) to Date and ID to ID, then you pull all the fields you want from table.

 

[sougata666]

You have nailed the problem, sir. But you really lost me with your solution. By linking, do you mean that in the 2nd query, i drag both the original table and the 1st query and then draw lines between the two date fields and ID fields? Please elaborate.

 

[pbaldy]

Yes. Does this help?

http://www.baldyweb.com/LastValue.htm

 

[plog]

By linking, do you mean that in the 2nd query, i drag both the original table and the 1st query and then draw lines between the two date fields and ID fields?

Yes, that's exactly it. A New query based on the previous query and your table, with lines drawn as you described. Then bring in all the data from the table that you want shown.

 

[sougata666]

Thank you all. Based on your suggestions, I have managed to implement it using two queries. I have posted what I have done on my other thread. However, I am now trying to get it done using a single query based on the same principle.

 

[pbaldy]

To me that makes it harder to work with. Basically you'd replace the query name in your SQL with the SQL of the first query.

FROM TableName INNER JOIN (SELECT...) As QueryName...