progress form help

[SoxPats83]

This qry will give you the tasks with null date (not completed) and sum the values. also give you the number of tasks each person has yet to complete.
I will give details of the tables I used.

SELECT QryTask01.Name, QryTask01.TaskDesc, Sum(QryTask01.TaskValue) AS SumOfTaskValue, QryTask01.Date, Count(QryTask01.TaskDesc) AS CountOfTaskDesc
FROM QryTask01
GROUP BY QryTask01.Name, QryTask01.TaskDesc, QryTask01.Date
HAVING (((QryTask01.Date) Is Null));

i'm sorry, i think you lost me

 

[PNGBill]

TablePerson - two fileds - PersonID and Name - used 4 names ie 4 records.
TblTask - 3 fields - taskID, TaskDesc and TaskValue - made 5 records.
and lastly TableTaskAssign - 4 fields - TaskAssignID, TaskID, PersonID and Date. created 15 records with 5 having a date and the balance no date.

TableTaskAssign would get it's records from a form you create where you can give a new task to a person.

Another form would allow you to enter a date when the task is complete - could be date time etc.

QryTask01 collects the data and QryTask02 uses QryTask01 as it's data source.

Trust this makes sence

 

[PNGBill]

See my notes on the tables I created - you provide similar info on your tables and I will use same to make your qry.

 

[PNGBill]

Here is a screen shot of my tables and there relationships if this assists.

 

[SoxPats83]

alright, with your help this is where i'm at: i have queried my query, and i now have a new query that groups the people together, and totals the task's values. problem is, the field where it would show which tasks are incomplete (which is know to be incomplete because the field is null), shows zero. i grouped the people together in my second query by selecting the totals option in ther menu, and then total: Group by, the values by Total= Sum, and Task Incomplete Total=Count. i believe the count is basically counting null fields, so naturally it came back with zero. what i want it to come back with is HOW MANY null fields there are.

possible?

 

[PNGBill]

If you want the sum of tasks not completed don't count the field that holds the "date" when a task is completed. Use this field to criteria the select query but use another field to count the number.
My count is on [TaskDesc] field.

 

[boblarson]

Here is a screen shot of my tables and there relationships if this assists.

IMPORTANT REMINDER:Try not to use DATE as a field name as it is an Access Reserved Word.

 

[PNGBill]

Here is my query in design view so you can clearly see the fields used.

 

[boblarson]

You can count null fields like this:

NullCount:IIF(IsNull([YourFieldName]),1,0)

and then use SUM in the grouping area.

 

[SoxPats83]

so basically there is no way to tell a query to count null records with a specific null field?

 

[boblarson]

so basically there is no way to tell a query to count null records with a specific null field?

Check my post above this one.

 

[PNGBill]

oops yes just rushed field name should be [TaskComp] or similar and then either by a date field, text or checkbox.

 

[SoxPats83]

You can count null fields like this:

NullCount:IIF(IsNull([YourFieldName]),1,0)

and then use SUM in the grouping area.

and i would enter this in the criteria?

 

[PNGBill]

If you filter Null in the first query:

SELECT TableTaskAssign.TaskAssignID, TblTask.TaskDesc, TablePerson.Name, TblTask.TaskValue, TableTaskAssign.CompDate
FROM TblTask RIGHT JOIN (TablePerson RIGHT JOIN TableTaskAssign ON TablePerson.PersonID = TableTaskAssign.PersonID) ON TblTask.taskID = TableTaskAssign.TaskID
WHERE (((TableTaskAssign.CompDate) Is Null));

And don't use the CompDate field in the 2nd query because you already know that all the records are not completed.

SELECT QryTask01.Name, QryTask01.TaskDesc, Sum(QryTask01.TaskValue) AS SumOfTaskValue, Count(QryTask01.TaskDesc) AS CountOfTaskDesc
FROM QryTask01
GROUP BY QryTask01.Name, QryTask01.TaskDesc;

 

[SoxPats83]

If you filter Null in the first query:

SELECT TableTaskAssign.TaskAssignID, TblTask.TaskDesc, TablePerson.Name, TblTask.TaskValue, TableTaskAssign.CompDate
FROM TblTask RIGHT JOIN (TablePerson RIGHT JOIN TableTaskAssign ON TablePerson.PersonID = TableTaskAssign.PersonID) ON TblTask.taskID = TableTaskAssign.TaskID
WHERE (((TableTaskAssign.CompDate) Is Null));

And don't use the CompDate field in the 2nd query because you already know that all the records are not completed.

SELECT QryTask01.Name, QryTask01.TaskDesc, Sum(QryTask01.TaskValue) AS SumOfTaskValue, Count(QryTask01.TaskDesc) AS CountOfTaskDesc
FROM QryTask01
GROUP BY QryTask01.Name, QryTask01.TaskDesc;

Sorry PNGBill, i mean no disrespect as you clearly have a MUCH stronger grasp on queries then i, but i think you are missing what the goal on my second query is. if my first query pulls all the date from a table that shows the person's ID, the tasks that are incomplete (null fields), and the value of each incomplete task, i want my second query to group each seperate ID as one, total their values (both of which i have already accomplished), and then to show how many incomplete tasks each ID has (null fields).

 

[PNGBill]

This version of query02 will give you Name, Task Count and Task value sum.

SELECT QryTask01.Name, Count(QryTask01.Name) AS CountOfName, Sum(QryTask01.TaskValue) AS SumOfTaskValue
FROM QryTask01
GROUP BY QryTask01.Name;

 

[PNGBill]

Remember, whatever query02 does, if it gets it's data from query01 and query01 filter out all the completed tasks then you are only working with and will get values for the not completed tasks.

 

[SoxPats83]

This version of query02 will give you Name, Task Count and Task value sum.

SELECT QryTask01.Name, Count(QryTask01.Name) AS CountOfName, Sum(QryTask01.TaskValue) AS SumOfTaskValue
FROM QryTask01
GROUP BY QryTask01.Name;

alright, i'm sorry. i now see where you are going. instead of counting null fields, i am counting how many times the ID is listed, which obviously equals the null fields. ugh, i should have picked up on that sooner, thank you very much!