can't update query

  • Thread starter Thread starter jonathan
  • Start date Start date
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"));
 
I don't understand why there are two tables with nearly identical
structures.

No summary query is ever going to be editable.

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
Hi, the two tables are almost teh same because they
contain the same info. It's used for tasking. You have a
main task heading, that then has lots of subtasks that
need to be completed. You can't close the main task unless
you close every subtask. Its done to produce a level of
control over tasking. As for the query it's the
recordsource for a form. On this form i need a column that
tells the user how many subtasks there are (without having
to actually open up the subtask form). If you can think of
a better pls be my guest, any ideas are welcome.
cheers
 
If a subtask can only have one main task, then you can store the MainTaskID
number in the table.

tblTask (main form)
TaskID
tasked_for
what_task
Task_created
Task_completed
MainTaskID (Self Refers to TaskID)

From there, the subform can display anything where the MainTaskID matches
the TaskID.(Do this in the Link Child/Master Fields of the subform.)

You can also have fun with Treeview controls and Visio charts, just
following the links to these articles:
http://www.advisor.com/articles.nsf/spec/clars02
http://www.advisor.com/articles.nsf/spec/clars03
http://www.advisor.com/articles.nsf/spec/clars04

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
Back
Top