Query without repetitions

[sougata666]

I have a table (say table1) like this in Access 2013


userid Category Date
------ -------- ----
1 A 1 MAY 13
1 B 2 MAY 14
2 B 1 MAY 12
3 NA 1 MAY 15
1 D 1 MAY 16
2 A 1 MAY 13
4 NA 1 MAY 14
5 C 1 MAY 12


i intend to design a query which will produce

userid Category Date
------ -------- ----
1 D 1 MAY 16
2 A 1 MAY 13
5 C 1 MAY 12

Conditions:
1. Only userids who do NOT have 'NA' in their category field to be selected.
2. Of those selected users, only their latest category to be displayed.

It is easy to get the first condition satisfied. However I am getting repeated userids (i.e. userid 1 is getting displayed multiple times. Same with other users.)

How can I design a single query to achieve the same? Also, I want to design it using ony the query designer without getting into SQL code view in Access if I can help it. I have really limited knowledge on SQL. I request a step by step answer especially if I have no other option but to use SQL. I have seen the top n in a group list answers but they are too SQL dependent and does not really help my case. I want an MS ACCESs 2013 specific answer.

 

[sougata666]

Sorry for the formatting. I am in a hurry actually. Basicailly userid is 1,2,3 etc and categories are A,B,C,D, NA.

 

[arnelgp]

SELECT DISTINCT T1.userid, (SELECT TOP 1 category FROM table1 WHERE userid = T1.userid ORDER BY table1.[date] DESC) AS Category, (SELECT TOP 1 [date] FROM table1 WHERE userid = T1.userid ORDER BY table1.[date] DESC) AS [date] FROM table1 AS T1 WHERE T1.category <> "NA";

 

[sougata666]

Thanks. Will try it out. But is it really not possible using just the access query designer GUI? I don't want to go into the Sql code window

 

[sougata666]

Also, what is this t1 thing? My table is table1. You are using both t1
table1.

 

[sougata666]

Sir, please explain why I need the alias T1. Can I use your statement without using AS?

 

[sougata666]

Also, I must mention that userid is not the primary key here. Will this change the sql statement?

 

[arnelgp]

T1 is just an alias for table1 on the main query (plz. google table aliasing).
we need to alias table1 since we use it again in the sub-query (the two queries in parenthesis).
on our sub-query we set a criteria that table1 userid should be equal to T1 userid.
we can't use table1 again since doing that well refer the condition to itself (table1.userid = table1.userid).

there is no need to modify the query (if you have tested it and if it is working).
has you have explained userid is repeating and on each repetition the category is changing (better/worst).

 

[sougata666]

Tried it. The category column is coming as blank

 

[arnelgp]

on my previous post there was a typo error on this part, and i edited it:

Where T1.cagegory<>"NA"

i now edited it to T1.category <> "NA".
copy and paste the code again in your sql view.

 

[sougata666]

Thanks but I had made the correction already. Didn't work

 

[arnelgp]

didn't work?
attached has same records on your sample.
run query1.

 

[sougata666]

Thank you sir, for your effort in helping me. Before I saw your file, I had managed to get the result after following the suggestion posted in my other thread. I found it easier as it didn't have your TOP expression and multiple subqueries. However, I intend to compact my own effort as it is easier for me to understand. Now that I have been able to get the result using multiple queries, I want to know whether I can implement it in a single query like yours but by employing the coding that I have used.

Here's what I have done:

EmployeeAll Query:

SELECT employee.employeeID, Max(employee.DateOfPromotion) AS LastDate
FROM employee
GROUP BY employee.employeeID;

EmployeeFinal Query (The 2nd query based on the previous one):

SELECT employee.*
FROM employee INNER JOIN employeeAll ON (employee.DateOfPromotion = employeeAll.LastDate) AND (employee.employeeID = employeeAll.employeeID)
WHERE (((employee.Category)<>"NA"));