addition expression

[Spidy6123]

If I'm using a simple expression to do addition of two columns on different tables based on a relation, how do I allow a figure to still go through even though at one record, one of the tables does not meet the relation.

Table one
column1 column2
joe 54
sam 24
harry 34

Table two
column1 column2
joe 24
sam 13


now in the query I have relations to show all records from table one and only the ones that table two matches set for both the column1's on both tables.

here are the results I get

joe 78
sam 37
harry

Harry is blank.. I need harry to show 34..

tia.. I'm a noober

 

[dhookom]

Use TableOne.Column2+Nz(TableTwo.Column2,0)

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[PHV]

Take a look at the Nz function:
SELECT A.column1, A.column2+Nz(B.column2,0) As NewCol
FROM [Table one] A LEFT JOIN [Table two] B ON A.column1=B.column1;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244