Grouping query

[HomeGrowth]

These are the records:

Name TaskName Complete
Tom 1 Yes
Tom 1 Yes
Tom 1 Yes
Tom 2 Yes
Tom 2 No
Tom 2 Yes

I need to create a query to indicate whether the person has completed the task or not, based on the Yes or No answer (the datatype is text for the Complete field). If the tasks are all 'Yes', the task is complete. If there is one 'No', then the task is 'No'.

Name TaskName Complete
Tom 1 Yes
Tom 2 No

Thanks for the help.

 

[dhookom]

Try something like:

SELECT [Name], TaskName, Min(Complete) as IsComplete
FROM [These are the records]
GROUP BY [Name], [TaskName];

Duane
Hook'D on Access
MS Access MVP

 

[HomeGrowth]

Thank you dhookom

Excellent query, it works great. Thanks again

 

[HomeGrowth]

One more question, based on the result, I got

Name TaskName Complete
Tom 1 Yes
Tom 2 No

Now, how can I do a tally like this in query?

Name #ofTask Yes No
Tom 2 1 1

Thank you in advance.

 

[dhookom]

Create a crosstab query from your previous query:

TRANSFORM Count([Name]) AS CountOfName
SELECT [Name], Count(TaskName) AS NumOfTasks
FROM qgrpTaskYesNo
GROUP BY [Name]
PIVOT IsComplete;

Duane
Hook'D on Access
MS Access MVP

 

[HomeGrowth]

dkookom Thanks again. I've learned so much.