Subtraction field

[drimades]

I have two tables in access (Table1 and Table2). How can I create a new table with a field sub that is a subtraction between two fields of the previous tables?

 

[r937]

could you first please show the SQL for a join between the tables?

also, how many columns, besides sub, if any, would you like this third table to have?

r937.com | rudy.ca

 

[drimades]

In "Create query table" I choose SQL:

SELECT field1 - field2 AS sub
FROM Table1, Table2;

 

[r937]

that's a cross join -- each row of table1 matched with every row of table 2

are you sure that's what you want? and only one column in the new table?

okay...

so your solution would be

select field1-field2 as sub
[b]into table3[/b]
from table1, table2

r937.com | rudy.ca

 

[drimades]

Thank you! It worked even previously. The problem is that I don't need a JOIN like this. Obviously, the result is a table that has a number of records that is a product between the nr of records. I need simply a field that is the difference between the other two ...

 

[r937]

remember when i asked for the SQL for a join between the tables, and you gave me a cross join ?

well, that's exactly what you get from a cross join -- the result is a table that has a number of records that is a product between the number of records

so i'm beginning to wonder if perhaps you really wanted an inner join after all

see

http://en.wikipedia.org/wiki/Join_(SQL)

for the difference between cross joins and inner joins

r937.com | rudy.ca