Query without repetitions

S

sougata666

Registered User.
Local time
Today, 04:35
Joined
May 1, 2016
Messages
36
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.
 
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.
 
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";
 
Last edited:
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
 
Also, what is this t1 thing? My table is table1. You are using both t1
table1.
 
Sir, please explain why I need the alias T1. Can I use your statement without using AS?
 
Also, I must mention that userid is not the primary key here. Will this change the sql statement?
 
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).
 
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.
 
didn't work?
attached has same records on your sample.
run query1.
 

Attachments

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"));
 
You must log in or register to reply here.

Similar threads

R
Merge two tables in query by date/time, preserving hourly time in one of the tables
Replies
12
Views
940
Ken Sheridan
Ken Sheridan
C
Query Help on "Lookup" Type Function
Replies
11
Views
1,168
arnelgp
arnelgp
H
Query combining records
Replies
13
Views
1,362
theDBguy
theDBguy
mloucel
Solved Interesting QUERY problem
Replies
11
Views
734
mloucel
mloucel
E
Help query Count
Replies
5
Views
801
cheekybuddha
cheekybuddha

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom