Aggregate functions

[El_Pablo]

Hi,

I'm having a problem trying to create calculated field.

I want to do a sum and average of the salaries, but all I get are empty fields.

Here is a simple database definition.

Table: Employees

Fields: pkEmployeeID, WageHour

Table: Weeks

Fields: pkWeekID, NumberOfHours, fkEmployeeID

Relation: pkEmployeeID - fkEmployeeID

How do I sum the total number of hours in a calculated field in the table employees?

I tried Sum(Weeks::NumberOfHours), but all I get is an empty field.

Any help would be great.

Thanx

[Søren Dyhr]

Must be an indexing problem with your foreignkey, otherwise should if be working, an alternative approach is to make Summary fields in the related, and pull them into the main tables layout. But if indexing or linking issues continues to exists are both techniques worthless.

Sometimes are type mismatch with the keyfields getting apparent jiffy-logical results, until you think further of the typecast taking place.

--sd

[dazlunn]

I can't see any problem with your current logic, it should work fine?

It's not something daft like the field width on the layout is not wide enough to display the result?

[El_Pablo]

Could it be that the values need to be refreshed like calculated field?

Another problem in the same order, why there are some fields that cannot be choose when I want to create a summary field? I have a billTotal field and I want to create a summary field, but I can't select it...

Thanx

[dazlunn]

I admit I'm struggling with this one...

There are limited things I can think of:

1 You are trying to (Sum) a text field

2 You are trying to (Summary) fields that are not native to the current table.

3 You are trying to sum records that the relationship contradicts.. although appears not

Make absolutely sure that the opportunities for errors, that Soren explains [above], are not your reality?!

[Søren Dyhr]

Perhaps you should tell us what type WageHour and NumberOfHour is, we more or less assumed that WageHour was the name of the summing field?

This is how I would do... more or less:

--sd

BZhummingbee.zip

[dazlunn]

Precision!

Soren, your example exactly demonstrates what we both assumed to be the logic in use..I think?!, and demonstrates that it works...

[El_Pablo]

All the fields are number types. I just found that there is a summary type field, can it help?

In the file you gave you use calculation, should I use this type?

Edited January 17, 2007 by Guest

[Søren Dyhr]

Yes I mentioned it in my first post, however exists some artifacts ... the field is now reluctant to freshen. But I have made the alterations to to the tables, so you can see for your self. Note that the field freshens if you place the cursor in it.

Measures exists to remedy this, but at what cost, when Sum( is handy??

--sd

BZColibree.zip

[El_Pablo]

Good! Now everything seems to work.

Thanks very much your help was useful!