Problem summing 3 fields in a query

[JohnP]

Seemed such a simple task when I started but ...

I have an elderly UNIX application on a RS6000 that has a poor report
writer so export/import and use ACCESS to produce Sales Reporting
info. I maintain a historical table of InvoiceLines and a daily
snapshot table of BackOrders.

I have 3 queries that extract and summarise from InvoiceLines and
BackOrders to give 3 temp tables with (a) SalesValue grouped by
ZipCode dated last year for a dieing product group and (b) SalesValue
grouped by ZipCode dated this year for an emerging replacement product
group and (c) BackOrderValue grouped by ZipCode for the new group.

In order to produce a 'converted business' report I combine tables A,
B, C with a query matching on ZipCode: All tableA and those from
tableB and tableC that match. This gives: ZipCode, ValueA, ValueB,
valueC. Obviously every record has a valueA but the other 2 fields are
scattered. I then want to add ValueB + ValueC to give a combined Value
that can be compared against ValueA.

If ValueB and ValueC both exist then no problem but if either or both
are missing then a total is not calculated.

The final 50'ish field query will be exported to Excel for
distribution.

In Excel it would be a simple sum(B1..C1) but how to do it in Access?

Sounds much more complex than it really is, basically I want to
express ThisYear Sales + Outstanding Orders of Product Brown as a
percentage of LastYear Sales of Product Orange, summarised by ZipCode.

Thanks in advance.

 

[John Vinson]

If ValueB and ValueC both exist then no problem but if either or both
are missing then a total is not calculated.

The final 50'ish field query will be exported to Excel for
distribution.

In Excel it would be a simple sum(B1..C1) but how to do it in Access?

Well, Excel is a spreadsheet. Access is a relational database. One's a
hammer, one's a wrench - and you can drive nails with a wrench, but
that doesn't make it a hammer!

Try NZ([ValueB]) + NZ([ValueC])