Query Calcations

[cinders]

I am using Access 2000

I need to subtract fields in one table from fields in a completely different table.

Once each of these pairs of fields in different tables are calculated, I need to take the sum of each pair and multiply each of them by different values.

Does anyone know how I would go about doing this??

 

[Alexandre]

When you are not sure that you can perform all your operations through a single query, you can divide your problem into simple steps. For example, calculate your differences through a first query and give a name to the query field containing the result. Then make a second query using the results of the first one...

Example:

TableA (One side of relationship)
- pkA (primary key)
- fieldA

TableB (Many side)
- pkA (foreign key)
- fieldB

Query1 (stores the differences into the field AminusB):
SELECT [TableA]![FieldA]-[TableB]![FieldB] AS AminusB
FROM TableA INNER JOIN TableB ON [TableA].[pkA]=[TableB].[pkA];

Query2 (calculates the sum of the differences AminusB and multiplies per 100):
SELECT Sum([AminusB])*100 AS Total
FROM Query1;


You can also consolidate results from different queries provided that they have a similar structure and a common field, through an union query (use the wizard)

Hope this helps

Alex

[This message has been edited by Alexandre (edited 08-28-2001).]