Calculating in query

[svjensen]

I need help to conduct calculations in a query.

I have the following tables:

tblNormer
=========
uid autonumber
Myndighed text
Aar number
Funktionsniveau text
Normer number

tblLoenKvanter
==============
uid autonumber
Myndighed text
Funktionsniveau text
Kvante number

I get the sum of 'Normer' shown per year using:
TRANSFORM Sum(tblNormer.Normer) AS SumOfNormer
SELECT tblNormer.Funktionsniveau
FROM tblNormer
GROUP BY tblNormer.Funktionsniveau
PIVOT tblNormer.Aar

This works fine!! And by inserting a WHERE clause I can limit this to a given 'Myndighed'.

I now need to calculate the total wage.
There is a wage rate ('Kvante') for each 'Funktionsniveau' for each 'Myndighed'.
The total wage for a given 'Myndighed' is then:
Sum(number of 'Normer' at 'Funktionsniveau'(1) * 'Kvante' for 'Funktionsniveau'(1) for this 'Myndighed') +
Sum(number of 'Normer' at 'Funktionsniveau'(2) * 'Kvante' for 'Funktionsniveau'(2) for this 'Myndighed') +
... +
Sum(number of 'Normer' at 'Funktionsniveau' * 'Kvante' for 'Funktionsniveau' for this 'Myndighed') +

I nees to be able to calculate the total wages for a given 'Myndighed', but also the total across all 'Myndighed'.

How can I achieve that?

/Soren

 

[Guus2005]

Problems are like little children.
If they are getting too complicated i tend to break them up in little pieces.
My kids are very happy that i don't have any.

What i am trying to say is that you need to create a query with a column in which you have the sum of 'Normer' at 'Funktionsniveau'(1), and another column for 'Funktionsniveau'(2) and so on.
In a second query you can multiply these columns by 'Kvante' for 'Funktionsniveau'(1) for this 'Myndighed'.
This should do the trick.

You might want to post a sample database describing the problem.

HTH