Sum based on multiple fields values

[Access Beginner12]

I am creating a database to view information of our clients companies, in this database I have a field [# of employees] containing the number of employees for each company. However some companies are groups, like a chain of companies. What I need to do is add another field that contains the total number of employees of a whole group. Every company has an [ID] and if it is below another company (which means it belongs in a group) then it has a [Parent ID] equal to the [ID] of the head company.



I have not yet been able to figure out how to do this for the whole database at once, I currently have a code that I must put in parameters (it works) but I have hundreds of records and do not want to input all of these parameters manually. Code here:

Public Function TotalEmployees(ID As String, EmployeeCount As Long) As Long

Dim rs As DAO.Recordset
Dim sql As String
Dim db As Database
Set db = CurrentDb

sql = "SELECT ID, [Parent ID], [# Of Employees] FROM Sheet1 WHERE [Parent ID] = '" & ID & "'"
' rs.Open sql, CurrentProject.Connection, adOpenKeyset, adLockReadOnly


Set rs = db.OpenRecordset(sql)



If Not (rs.EOF And rs.BOF) Then
rs.MoveFirst 'Unnecessary in this case, but still a good habit
Do Until rs.EOF = True


EmployeeCount = EmployeeCount + TotalEmployees(rs.Fields("ID"), rs.Fields("# Of Employees"))


'Move to the next record. Don't ever forget to do this.
rs.MoveNext
Loop


TotalEmployees = EmployeeCount
End If


Debug.Print TotalEmployees


rs.Close
Set rs = Nothing
End Function

My question is how can I add up the employees in one simple code? Does not matter if this is done in SQL view of a query, a macro, a calculated field etc. I am just looking for the best option.

Thank you very much

[Bulzie]

Create a query to Group on ParentID and Sum on [# of Employees] to give you all employees for the Company Group
Create a query to Group on ID and Sum on [# of Employees] to give you all employees for each individual Company

Can link those queries back to the main table to link it to the records.