Sum Fields which have blank records

A

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?
 
A

amos

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
 
D

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
--

amos said:
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

amos said:
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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top