Calculate fields using aggregate expression

[Nelly]

Hello all,

I have a League Table which I am trying to automatically update using
functions that calculate the sum of fields in other tables based on a
Name field in the League Table table.

So, for example 'name1' in the league table should equal the sum of the
Hours field in the Register table where 'name1' equals 'name' in the
'Employee' table which is related to the Register table.

There are only 4 records in the League Table one for each possible
'name', but I need the result in a format that can be ordered,
otherwise I would just stick edit boxes on a form.

Perhaps there is an easier way of achieving this ordered data set. I
need a form with an ordered league table result based on various hours
worked/absent etc.

The league table is currently linked to the Supervisor table in the
following hierarchy:

Supervisor -> Employee -> Register

The league table does not need to be linked, as long as I get the
output. I have just linked it just now to test queries etc.

Any ideas most greatly accepted.

many thanks in advance.

 

[tina]

a standard rule in database design is: don't store calculated values in
tables; store the raw data and perform the calculations as needed at
runtime. there a certain instances where this rule must be broken for
business or technical reasons, but i doubt that either applies here.

just use a query to link your "other tables", and calculate the sums there.
you can use the query as the base for a form or report, to display the
results whenever and however you need to.

hth

 

[Nelly]

Hi Tina,

Yes, I realise this and the 'table' was me trying out different things
because I was getting a bit lost. It was against my better judgement.

I still have the problem of using a query. How do I get it in a format
so I have 4 records for four 'names' so I can sort them in order before
sticking them on a form?

I know how to calculate the required results but they just populate
into one record in the query result. I am a bit of a novice, it seems.

Hope I am explaining this correctly.

Thanks again.