Sum Fields which have blank records

[Guest]

Hi everyone,

I have joined 2 separate tables which in their original state held in some
instances distinct data for each other ie

table 1
record field1
A 2
B
C 2

table 2
record field2
A
B 1
C 3


joins to....
record field1 field2
A 2
B 1
C 2 3

Now when I try to sum field1 and field2 together (using both sum and +
commands), I only get a result for record C = 5. Record A and B return blank
results.

I've tried the online documentation but could find nothing, would appreciate
any help, I know its got to do with the fact with the fact that I'm asking
Access to add a record which doesn't exist...also any answer possible using
SQL as opposed to using a nested Iif?

 

[Guest]

I can complete using nested Iif statements ie
IIf(IsNull([field1]),0,[field1]), but wondering if anyone knows how to
compute using native SQL

cheers,
Amos

 

[Duane Hookom]

Create a union query:
SELECT Record, Field1 as TheValue
From Table1
UNION ALL
SELECT Record, Field2
FROM Table2;

You can then create a totals query from the union query that groups by
Record and Sum()s TheValue.

--
Duane Hookom
MS Access MVP
--

I can complete using nested Iif statements ie
IIf(IsNull([field1]),0,[field1]), but wondering if anyone knows how to
compute using native SQL

cheers,
Amos

Hi everyone,

I have joined 2 separate tables which in their original state held in
some
instances distinct data for each other ie

table 1
record field1
A 2
B
C 2

table 2
record field2
A
B 1
C 3


joins to....
record field1 field2
A 2
B 1
C 2 3

Now when I try to sum field1 and field2 together (using both sum and +
commands), I only get a result for record C = 5. Record A and B return
blank
results.

I've tried the online documentation but could find nothing, would
appreciate
any help, I know its got to do with the fact with the fact that I'm
asking
Access to add a record which doesn't exist...also any answer possible
using
SQL as opposed to using a nested Iif?