DCount - Query (I Think)

[gripper]

I'm not sure where I need to go on this since this is my first attempt at trying to get a total.

I have a table "Domain" it holds various website information and within that table I have certain tasks that need to be completed. Specifically 3 task I am focusing on here "task1, task2, task3" Each of these task is a simple check box (yes/no)

What I want to do on Form DomainDetail is have a field or label "txtTaskCount". Within that task count box I want a number of the items (task 1 -3) that had NOT been completed. (ie the check box is not checked yet) For instance if Task #1 is finished then it would give me a number of 2 indicating two tasks had not been completed as of yet (Task 2 & 3). Also lets say that only task 2 is completed it will see that Task 1 and Task 3 is still outstanding and give me a total of 2 task that needs completion) Some task are not taken in any particular order.

Where I am lost is I have been playing with queries and not getting it so perhaps VB Code is better but I will take either one at this point.

Suggestions would be greatly appreciated.

 

[Norie]

What fields do you have in your table?

Do you want a count of records where task X is not completed/completed?

Or is it a count of tasks that have been completed/not completed for each record?

This expression would give you the number of tasks completed.

CompletedTasks:Abs([Task1]+[Task2]+[Task3])

This will only work if the fields Task1, Task2 are Yes/No fields.

Let's say you have a project table, like this.

<TABLE border=1 cellSpacing=0 bgColor=#ffffff><CAPTION>tblTasks</CAPTION><THEAD><TR><TH bgColor=#c0c0c0 borderColor=#000000>Project</TH><TH bgColor=#c0c0c0 borderColor=#000000>Task1</TH><TH bgColor=#c0c0c0 borderColor=#000000>Task2</TH><TH bgColor=#c0c0c0 borderColor=#000000>Task3</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#eeece1>1</TD><TD borderColor=#eeece1 align=right>True</TD><TD borderColor=#eeece1 align=right>True</TD><TD borderColor=#eeece1 align=right>False</TD></TR><TR vAlign=top><TD borderColor=#eeece1>2</TD><TD borderColor=#eeece1 align=right>False</TD><TD borderColor=#eeece1 align=right>True</TD><TD borderColor=#eeece1 align=right>False</TD></TR><TR vAlign=top><TD borderColor=#eeece1>3</TD><TD borderColor=#eeece1 align=right>True</TD><TD borderColor=#eeece1 align=right>False</TD><TD borderColor=#eeece1 align=right>True</TD></TR><TR vAlign=top><TD borderColor=#eeece1>4</TD><TD borderColor=#eeece1 align=right>False</TD><TD borderColor=#eeece1 align=right>True</TD><TD borderColor=#eeece1 align=right>True</TD></TR><TR vAlign=top><TD borderColor=#eeece1>5</TD><TD borderColor=#eeece1 align=right>True</TD><TD borderColor=#eeece1 align=right>True</TD><TD borderColor=#eeece1 align=right>True</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

The board sotfware has replaced the checkboxes with True/False.

Now to get these results:

<TABLE border=1 cellSpacing=0 bgColor=#ffffff><CAPTION>qryCompletedTaskCnt</CAPTION><THEAD><TR><TH bgColor=#c0c0c0 borderColor=#000000>Project</TH><TH bgColor=#c0c0c0 borderColor=#000000>Task1</TH><TH bgColor=#c0c0c0 borderColor=#000000>Task2</TH><TH bgColor=#c0c0c0 borderColor=#000000>Task3</TH><TH bgColor=#c0c0c0 borderColor=#000000>CompletedTasks</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#d0d7e5>1</TD><TD borderColor=#d0d7e5 align=right>True</TD><TD borderColor=#d0d7e5 align=right>True</TD><TD borderColor=#d0d7e5 align=right>False</TD><TD borderColor=#d0d7e5 align=right>2</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>2</TD><TD borderColor=#d0d7e5 align=right>False</TD><TD borderColor=#d0d7e5 align=right>True</TD><TD borderColor=#d0d7e5 align=right>False</TD><TD borderColor=#d0d7e5 align=right>1</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>3</TD><TD borderColor=#d0d7e5 align=right>True</TD><TD borderColor=#d0d7e5 align=right>False</TD><TD borderColor=#d0d7e5 align=right>True</TD><TD borderColor=#d0d7e5 align=right>2</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>4</TD><TD borderColor=#d0d7e5 align=right>False</TD><TD borderColor=#d0d7e5 align=right>True</TD><TD borderColor=#d0d7e5 align=right>True</TD><TD borderColor=#d0d7e5 align=right>2</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>5</TD><TD borderColor=#d0d7e5 align=right>True</TD><TD borderColor=#d0d7e5 align=right>True</TD><TD borderColor=#d0d7e5 align=right>True</TD><TD borderColor=#d0d7e5 align=right>3</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

We can use a query like this.

SELECT tblTasks.Project, tblTasks.Task1, tblTasks.Task2, tblTasks.Task3, Abs([Task1]+[Task2]+[Task3]) AS CompletedTasks
FROM tblTasks;

 

[gripper]

I was able to get the select query to work as a stand alone query. What I want to do is have a field in my form that when I am on a particular record it will give me the results of that query for that one record. Then when I move on to the next it will do the same and so on.

I created a field in my table named "CompletedTasks" and then on my form I created a field that uses this as the control source. Nothing show up.

Could you provide advice on how to get all that to sync into a form field.

I appreciate your help thus far very much

Thanks

 

[Norie]

Did you try the query I posted as the source for a form?

 

[gripper]

On a form I have a bunch of domain fields. I just created a text box "CompletedTasks" to hold the value in the Domain table and place a textbox on the form.

In that box under control source I put the select query directly copied from the SQL on the query design.

In the stand alone query it does fine. I am just trying to get the number of task on each record but it is not coming together. The text box is blank

I am missing a step here somewhere.

Any thoughts.

Thanks

 

[Norie]

You don't use an entire query for the control source.

Try just using the expression I posted, or similar, for the control source.

Just replace the : and everything before it with =.

 

[gripper]

That worked perfectly, Thanks for you help and patience