how to use distinct in ms access

bsm picture bsm · Apr 26, 2011 · Viewed 99.9k times · Source

I have two tables. Task and Categories.

Task Table

Categories Table

TaskID is not a primary key as there are duplicate values.When there are multiple contacts are selected for a specific task,taskid and other details will be duplicated.I wrote the query:

SELECT Priority, Subject, Status, DueDate, Completed, Category
FROM Task, Categories
WHERE Categories.CategoryID=Task.CategoryID;

Query Result

Now as multiple contacts are selected for that task,for the taskid=T4, there are two records(highlighted with gray). I have tried using distinct in ms access 2003 but its not working. I want to display distinct records. (Here there's no requirement to show taskid) If I write :

select priority, distinct(subject), .......

and remaining same as mentioned in above query then its giving me an error. I have tried distinctrow also.But didnt get success. How to get distinct values in ms access?

Answer

bsm picture bsm · Apr 26, 2011

Okay.Its working this way.

SELECT DISTINCT Task.Priority, Task.Subject, Task.Status, Task.DueDate, 
Task.Completed, Categories.Category
FROM Task, Categories
WHERE (((Categories.CategoryID)=[Task].[CategoryID]));