Append Query

J

J.A.C.

Access 2003
I created an append query from a cross tab query - the objective is to add
the summed totals of five different categories to a Table which is used in an
input form and many reports – that calculates the five fields. The problem
is that when it appends to the table some of the fields are blank (no balance
in them) unless the fields have a value they will not calculate the total of
these fields. I have set the default “0†however when I append to the Table
the categories that do not have a value are blank and unless I manually input
the "o" the calculated will not work - it will also be blank.
Is there someway of adding the “o†values at the append query level?
 
F

frogsteaks

Access 2003
I created an append query from a cross tab query -  the objective is to add
the summed totals of five different categories to a Table which is used inan
input form and many reports – that calculates the five fields.  The problem
is that when it appends to the table some of the fields are blank (no balance
in them) unless the fields have a value they will not calculate the total of
these fields.  I have set the default “0” however when I append to the Table
the categories that do not have a value are blank and unless I manually input
the "o" the calculated will not work - it will also be blank.
Is there someway of adding the “o” values at the append query level?

On any field in a query you can use someething like this...


ItemTotal: iif(isnull(sum(field)),0,sum(field))

(May have to clean up that syntax a bit. It has been a while since I
have used that. It will create a field in the output called ItemTotal
that contains the Summed value of 'field' or (in the case of no data
in 'field') 0.
 
J

J.A.C.

I need more specific instructions - where on the query do I insert this
statement?

I am fairly new at this and have tried putting it on the source - cross tab
query and then on the append query and neither seems to work.

Sorry about my lack of experience.
 
J

J.A.C.

Here is what the help on Zero Balances states"When you have fields that
contain Null values, you can create an expression (expression: Any
combination of mathematical or logical operators, constants, functions, and
names of fields, controls, and properties that evaluates to a single value.
Expressions can perform calculations, manipulate characters, or test data.)
that converts the Null values to zero. You might do this if you want the
records containing Null values to be included in an aggregate (aggregate
function: A function, such as Sum, Count, Avg, or Var, that you use to
calculate totals.) calculation, or if you want to prevent an expression from
resulting in a Null value when a field that's referenced in the expression
contains Null values. Use the Nz function, for example, to convert Null
values to zero:

Nz([Subtotal],0+Nz([Freight],0)"

My question - Where - What Line - on the Query is this information -
Expression placed?

Thanks
 

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