Aggregate Expression Returning NULL

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a strongly typed dataset with 3 related tables:

PayrollChecks (Parent)
PayrollCredits (Child of PayrollChecks 1 to many)
PayrollDeductions (Child of PayrollChecks 1 to many)

I can loop through the PayrollChecks table and using the relationships print
out the Credits and Deductions for each check so the DATA IN THE TABLES IS
CORRECT and there are NO NULLS.

Adding a Credits aggregate sum field to the PayrollChecks table returns the
correct data.

Adding a Deductions aggregate sum field to the PayrollChecks table always
returns NULL - there are NO NULL records in the child table.

The PayrollChecks table has a multi-part primary key defined - the Child
tables do NOT have primary keys defined as there are no unique field
combinations. The child tables do have the same fields as the parents primary
key.

I have crawled all over this thing deleting and re-adding relationships,
changing the fetch order, renaming relationships and have come up ZIP!

Anyone seen something like this or know where else I might look?
 
After 4 hours and much knashing of teeth I have uncovered a work around for
this problem.

The reason the aggregate sum on the PayrollCredits was working is that it
had a field that referenced the Parent using an expression of Parent.PayType.

When I added the same field to the PayrollDeductions table the Deductions
Aggregate started working.

This is certainly something that Microsoft should look into - without an
expression field referencing back to the Parent the Aggreate functions will
not work on child tables without Primary Keys.
 
Back
Top