How to use different access to ms

Question

I have two tables. Task and categories.

TaskID is not a primary key because duplicate values ​​exist. When multiple contacts are selected for a particular task, taskid and other data will be duplicated. I wrote a request:

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

Now that several contacts are selected for this task, there are two entries for taskid = T4 (grayed out). I tried to use different access in ms 2003, but it did not work. I want to display individual entries. (There is no requirement to show taskid) If I write:

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

and staying the same as indicated in the previous request, this gives me an error. I also tried to distinguish myself. But did not get success. How to get different values ​​in ms access?

Answer 1

Good. It works that way.

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

Answer 2

I do not like to use SELECT DISTINCT, I found that my code takes longer to compile. In another way, I do this using GROUP BY.

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

I don't have VBA at the moment, but this should work too.

Answer 3

Using SELECT DISTINCT will work for you, but the best solution here is to redesign the database.

Duplicate entries may result in inconsistent data. For example, imagine that you have two different statuses in different records with the same TaskID. Which one would be right?

A better design would include something like a task table, contact tables, and assignment tables, as shown below (fields in brackets are PK):

Tasks: [TaskID], TaskPriority, Subject, Status, DueDate, Completed, StartDate, Owner, CategoryID, ContactID, ...

Contact: [ID], first name, last name, address, phone number, ...

Destination: [TaskID, ContactID]

You can then get Tasks with a simple SELECT from the Tasks tables. And whenever you need to know the contacts assigned to tasks, you must do this with a JOIN clause like this

 SELECT T.*, C.* FROM TaskID as T INNER JOIN Assignment as A ON T.TaskID = A.TaskID INNER JOIN Contac as C ON A.ContactID = C.ID 

Or similarly. You can filter, sort or group results using all the features of SQL queries.