Re: Sum Fields which have blank records

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" <amos@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8CFDBD5F-20CE-4046-ABFC-D6F709523B5C@xxxxxxxxxxxxxxxx
>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" wrote:
>
>> 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?


.

Relevant Pages