Queryquiz

  • Thread starter Thread starter Svein
  • Start date Start date
S

Svein

Info: A2k

Does anyone have a suggestion on how to aggregate
ChildrenCount in a detail relationship and put the result
into a field in the main table in an update statement?

Example:

Two tables A and B in a parent-child relationship.

A has the parents names plus a field for how many children
they have in the B table. I want to count up how many
children the parents have and put it in a field in the
parent table.
To update the A.CountOfChildren field, what would best
replace the following statement that of course doesn't
work for the Jet engine:

"Update A set A.CountOfChildren = Select Count(*) from B
inner join A on A.ID = B.ParentID;"

Let's say table A has the following fields:
ID Autonumber
ParentName Text(50)

and B has the following fields:
ID Autonumber
ParentID Number
ChildName Text(50)
 
Try using the DCount function

UPDATE TableA
SET TableA.CountOfChildren = DCOUNT("*","TableB","ParentID=" & A.ID)
 
Back
Top