How to sum two fields each from two different tables ?

[hideaki25]

Say two tables have exact same table structure:
Table 1
Food Price Tax

Table 2
Food Price Tax

How do you get the sum of

food (table1.price+table2.price) (table1.tax+table2.tax)

I'm very familiar with SQL writing using Oracle but I'm having a ton of problems trying to do this in Access.

Any help would be greatly appreciated !

 

[Sawatzky]

dear hideaki,

Assuming the tables are linked by the field food

try this:

select table1.price + table2.price as price, table1.tax + table2.tax as tax
from table1,table2
where table1.food = table2.food

regards Astrid

 

[hideaki25]

I think that I'm having problems more with the syntax than anything else. I'm using Access 97 version and trying to code in the Access SQL window of the query tab.

In the particular tables that I have there are three tables say:
Input 1, Input 2, Total Table

All three tables have three same columns:
ID, Num, CashFlow - of these the primary keys are id and num.

If I wanted input1.cashflow + input2.cashflow in total_table.cashflow then do I link the two primary keys of the input tables together first ( this I think creates inner joins on these fields ) and then proceed ?

Writing SQL Code in the query tab SQL view is a nightmare when I don't know the syntax. I believe if I setup the above tables the SQL view produces the following SQL framework:

SELECT
FROM [Input - Entity] INNER JOIN [Input - Fee] ON ([Input - Fee].[Line Item] = [Input - Entity].[CF Line Item]) AND ([Input - Entity].[Tracking ID] = [Input - Fee].[Tracking ID]);

I was not successful at fitting the code you suggested into this framework. Perhaps I should not use this framework ?

Thanks for the response !

 

[Sawatzky]

dear hideaki25,

you could also use the assistance of the access environment.

if you are not in the sql- but in the 'normal' editing view of a query and you right-click on a free output column there you have an option, which is called "aufbauen" in German and I asume somthing with build in English.

then a window pops up where you can generate your function by clicking.


And I assume you want to create an Update query rather than a select query.


HTH
regards Astrid

 

[VBmim]

Maybe you should try this:

SELECT ([Input - Entity].price + [Input - Fee].price) as SumOfPrice, ([Input - Entity].tax + [Input - Fee].tax) as SumOfTax, *
FROM [Input - Entity] INNER JOIN [Input - Fee] ON ([Input - Fee].[Line Item] = [Input - Entity].[CF Line Item]) AND ([Input - Entity].[Tracking ID] = [Input - Fee].[Tracking ID]);

I hope it helps, let me know!

greetings

Mim