For Each ... Next loop

[CLSWL]

I don't know the syntax to make this work... everything surrounded by * is
unknown

My aim is for the project status to update based on the status of all the
associated tasks. I have a table with all my projects and their name, point
person, and status.

I actually have 2 more levels of data.
I have a table that links each project to several jobs.
I have a table that links each job to several tasks which have an assigned
person and a status. I want the status of these tasks to determine the
status of the overall project.


Public Function CountStatus()
Dim Count1 As Long 'Counts tasks that are not started
Dim Count2 As Long 'Counts tasks that are in work
Dim Count3 As Long 'Counts tasks that are queued
Dim Count4 As Long 'Counts tasks that are complete
Dim Count5 As Long 'Counts tasks that are cancelled
Dim CountAll As Long 'Counts all tasks of a project

For Each *project* In *table of projects*
Count1 = 0
Count2 = 0
Count3 = 0
Count4 = 0
Count5 = 0
CountAll = 0
For Each *task* In *table of project tasks*
If *task status* = 1 Then
'Count1 = Count1 + 1
ElseIf *task status* = 2 Then Count2 = Count2 + 1
ElseIf *task status* = 3 Then Count3 = Count3 + 1
ElseIf *task status* = 4 Then Count4 = Count4 + 1
ElseIf *task status* = 5 Then Count5 = Count5 + 1
End If
CountAll = CountAll + 1
Next
If CountAll = Count5 Then
*project status* = 5
ElseIf CountAll = Count4 Then *project status*= 4
ElseIf CountAll = Count3 Then *project status*= 3
ElseIf CountAll = Count1 Then *project status*= 1
ElseIf CountAll = Count4 + Count5 Then *project status*= 4
ElseIf CountAll = Count1 + Count3 + Count4 + Count5 Then
*project status*= 3
Else: *project status*= 2
End If
Next

 

[Steve Sanford]

It won't work. The syntax "For Each ... In ..." is for collections. Your data
is in tables. Also, there is no way to relate "tasks" to "Projects".

I would use two recordsets and loop thru them.

What are the names of the tables and the fields in the tables?
How are the tables related (linking fields)?

 

[John Spencer]

You might try looking into a crosstab query to get the information

'First Query saved as QCrosstab
TRANSFORM CLng(Nz(Count(T.Status),0)) as StatusCount
SELECT T.ProjectID
, Count(T.ProjectID) as TaskCount
FROM ProjectsTable as P INNER JOIN TasksTable As T
ON P.ProjectID = T.ProjectID
GROUP BY T.ProjectId
PIVOT "S" & T.Status in ("S1","S2","S3","S4","S5")

Then using that as the source you could build another query to display the
results

'Next query saved as qFinalResult
SELECT ProjectID,
SWITCH (S5=TaskCount,5
S4=TaskCount,4
S3=TaskCount,3
S1=TaskCount,1
S4+S5=TaskCount,4
S1+S3+S4+S5,3
True,2) as ProjectStatus
FROM QCrosstab

IF you need to update project status in the Projects table, you could save
the above to a temp table and then update the Projects table from the temp
table. OR you might be able to use DLookup against the above table in a
query that looks like the following

UPDATE Projects
Set Projects.Status = DLookup("ProjectStatus","QFinalResult","ProjectID=" &
Projects.ProjectID)

WARNING. With a large record set this could be very slow.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[CLSWL]

I know even less about using recordsets that I do about For Each Next loops

I started trying to use it but all i have is

Dim rst As New ADODB.Recordset
rst.Open "ProjInfo"


And about my tables... they are related already.


ProjID is key on the ProjInfo table
ProjJobID is key on the ProjWork table
ProjTaskId is key on the ProjTask table

1 project to many jobs
1 job to many tasks

How exactly do i loop through a recordset. And how do i do nested loops?
Syntax is really what i need.

 

[Steve Sanford]

It would have been easier if you had provided the field names so I could see
the linking field names. So I had to guess on the field names:

1 -> many
ProjInfo ProjWork
--------- -----------
ProjID -> ProjID

ProjWork ProjTask
----------- -----------
ProjJobID -> ProjJobID


So using the above linking fields, here is the code I came up with:

(untested) (watch for line wrap)

'-----------beg code-------------------
Public Sub CountStatus()
On Error GoTo Err_CountStatus

'Dim Count1 As Long 'Counts tasks that are not started
'Dim Count2 As Long 'Counts tasks that are in work
'Dim Count3 As Long 'Counts tasks that are queued
'Dim Count4 As Long 'Counts tasks that are complete
'Dim Count5 As Long 'Counts tasks that are cancelled
'Dim CountAll As Long 'Counts all tasks of a project

Dim db As DAO.Database
Dim rsProj As DAO.Recordset
Dim rsTask As DAO.Recordset
Dim strSQLProj As String
Dim strSQLTask As String

Dim NotStarted As Long 'Counts tasks not started Count1
Dim InWork As Long 'Counts tasks in work Count2
Dim queued As Long 'Counts tasks queued Count3
Dim complete As Long 'Counts tasks complete Count4
Dim cancelled As Long 'Counts tasks cancelled Count5
Dim AllTasks As Long 'Counts all tasks for proj CountAll

Set db = CurrentDb

strSQLProj = "SELECT ProjID, [project status] FROM ProjInfo"

Set rsProj = db.OpenRecordset(strSQLProj)

If rsProj.BOF And rsProj.EOF Then
MsgBox "No Projects found"
rsProj.Close
Set rsProj = Nothing
Set db = Nothing
End If

rsProj.MoveFirst
Do While Not rsProj.EOF
'For Each *project* In *table of projects*
NotStarted = 0
InWork = 0
queued = 0
complete = 0
cancelled = 0
AllTasks = 0

strSQLTask = "SELECT ProjTask.[Task Status]"
strSQLTask = strSQLTask & " FROM ProjWork INNER JOIN ProjTask"
strSQLTask = strSQLTask & " ON ProjWork.ProjJobID = ProjTask.ProjJobID"
strSQLTask = strSQLTask & " WHERE (((ProjWork.ProjID)= " & rsProj!ProjID
& "));"

Set rsTask = db.OpenRecordset(strSQLTask)

If Not rsTask.BOF And Not rsTask.EOF Then
rsTask.MoveFirst
Do While Not rsTask.EOF

Select Case rsTask![task status]
Case 1
NotStarted = NotStarted + 1
Case 2
InWork = InWork + 1
Case 3
queued = queued + 1
Case 4
complete = complete + 1
Case 5
cancelled = cancelled + 1
End Select
AllTasks = AllTasks + 1
rsTask.MoveNext
Loop
rsTask.Close

'update rsProj![project status]
rsProj.Edit
Select Case AllTasks
Case cancelled
rsProj![project status] = 5
Case complete
rsProj![project status] = 4
Case queued
rsProj![project status] = 3
Case NotStarted
rsProj![project status] = 1
Case complete + cancelled
rsProj![project status] = 4
Case NotStarted + queued + complete + cancelled
rsProj![project status] = 3
Case Else
rsProj![project status] = 2
End Select
rsProj.Update
End If
rsProj.MoveNext
Loop
rsProj.Close

Exit_CountStatus:
Set rsProj = Nothing
Set rsTask = Nothing
Set db = Nothing
Exit Sub

Err_CountStatus:
MsgBox Err.Description
Resume Exit_CountStatus

End Sub
'-----------end code-------------------


HTH