J
jonathan
Sorry if this is a double post, i did;nt think it posted the first time
Hi, i finally got a query to work how i want, its also a recordsource for a
form that has a subform on it.
I have 2 tables with a 1 to many relationship using TaskID.
Table structures are
tblTask (main form)
TaskID
tasked_for
what_task
Task_created
Task_completed
tblGroupTask (subform)
ID
TaskID
tasked_for
what_task
Task_created
Task_completed
The query brings back all tasks for a certain person in tblTask. This query
then has a second query in it that has a recordcount column that tells me
how many related records there in tblGroupTask based on TASKID been the same
in both tables. The recordcount column is then placed in my main query to
give me the results which is great. Problem is this neesd to be an editable
query and it's not, how can i acheive this???
recordcount TaskID Tasked_for What_task
2 1000 ME test
1 1001 Jon test
These results tell me TASKid 1000 has 2 subtasks and so on.
This is my main query
SELECT qryAnysubTasks.RecordCount, tasks.TaskID, tasks.JobID,
tasks.Tasked_for, tasks.What_task, tasks.Task_due, tasks.last_contact,
tasks.next_contact, tasks.task_completed, tasks.priority_level,
tasks.task_notes
FROM tasks LEFT JOIN qryAnysubTasks ON tasks.TaskID = qryAnysubTasks.TaskID
WHERE (((tasks.task_completed)="no"))
ORDER BY tasks.Task_due;
This is my subquery that does the recordcount
SELECT DISTINCTROW Count(*) AS RecordCount, tblgrouptasks.TaskID
FROM tasks INNER JOIN tblgrouptasks ON tasks.TaskID = tblgrouptasks.TaskID
GROUP BY tblgrouptasks.TaskID, tblgrouptasks.task_completed
HAVING (((tblgrouptasks.task_completed)="no"));
Hi, i finally got a query to work how i want, its also a recordsource for a
form that has a subform on it.
I have 2 tables with a 1 to many relationship using TaskID.
Table structures are
tblTask (main form)
TaskID
tasked_for
what_task
Task_created
Task_completed
tblGroupTask (subform)
ID
TaskID
tasked_for
what_task
Task_created
Task_completed
The query brings back all tasks for a certain person in tblTask. This query
then has a second query in it that has a recordcount column that tells me
how many related records there in tblGroupTask based on TASKID been the same
in both tables. The recordcount column is then placed in my main query to
give me the results which is great. Problem is this neesd to be an editable
query and it's not, how can i acheive this???
recordcount TaskID Tasked_for What_task
2 1000 ME test
1 1001 Jon test
These results tell me TASKid 1000 has 2 subtasks and so on.
This is my main query
SELECT qryAnysubTasks.RecordCount, tasks.TaskID, tasks.JobID,
tasks.Tasked_for, tasks.What_task, tasks.Task_due, tasks.last_contact,
tasks.next_contact, tasks.task_completed, tasks.priority_level,
tasks.task_notes
FROM tasks LEFT JOIN qryAnysubTasks ON tasks.TaskID = qryAnysubTasks.TaskID
WHERE (((tasks.task_completed)="no"))
ORDER BY tasks.Task_due;
This is my subquery that does the recordcount
SELECT DISTINCTROW Count(*) AS RecordCount, tblgrouptasks.TaskID
FROM tasks INNER JOIN tblgrouptasks ON tasks.TaskID = tblgrouptasks.TaskID
GROUP BY tblgrouptasks.TaskID, tblgrouptasks.task_completed
HAVING (((tblgrouptasks.task_completed)="no"));