Not equal to zero


J

Jessy MM Jules

Hi
I have access 2007 and have set up a simple table called orders. Within the
table I have set up Order ID, Order Date, Cutomers Name, Total Sales
(currency), Payment Amount (currency), Payment date. I have entered the
revelvant details.

I have set up a query where I have have selected the Customer name, total
sales, payment amount from the table above. In design view I have added a
new column and used the expression builder where I have used total sales -
payment amount and have got my answer which is all correct (negative totals
included ie paid more than owe).

Now I would like to show only those amounts that have are not zero and can
not seem to figure out how to do this. I feel it should be simple but I have
tried >0 in the crieteria etc and many other variations including using the
expression builder again in the criteria field, but it is not returning what
I would like.

Thanks for any help
Jessy
 
Ad

Advertisements

J

John W. Vinson

Now I would like to show only those amounts that have are not zero and can
not seem to figure out how to do this. I feel it should be simple but I have
tried >0 in the crieteria etc and many other variations including using the
expression builder again in the criteria field, but it is not returning what
I would like.

well... what exactly ARE you doing? Could you post the SQL of the query,
indicate something about the data in the table, what it's returning and what
it should be returning?

If you might have NULL values they're not equal to zero; and if you have
negative values they're of course not greater than zero. A criterion of

<> 0 OR IS NULL

may be more appropriate.
 
J

Jessy MM Jules

Hi John (thanks for responding :) )

This is what I have been asked to do for my assignment.

***Create a query (or calculated field) to calculate 'Outstanding balance'.
'Outstanding balance' is 'Total Sale' minus 'Payment amount'.
Select those records where the balance is 'not equal to' zero.***

The query will be based on the table which has Order ID, Order Date,
Customer Name, Total Sale, Payment Amount, Payment Date.

Not sure what you mean by SQL but I have copied this from the SQL view in
the query:

SELECT Orders.CustomerName, Sum(Orders.TotalSale) AS SumOfTotalSale,
Sum(Orders.PaymentAmount) AS SumOfPaymentAmount,
[SumOfTotalSale]-[SumOfPaymentAmount] AS [Outstanding Balance]
FROM Orders
GROUP BY Orders.CustomerName;

Regards
Jessy
 
Ad

Advertisements

J

John W. Vinson

Hi John (thanks for responding :) )

This is what I have been asked to do for my assignment.

Well... it's YOUR homework not mine...
***Create a query (or calculated field) to calculate 'Outstanding balance'.
'Outstanding balance' is 'Total Sale' minus 'Payment amount'.
Select those records where the balance is 'not equal to' zero.***

The query will be based on the table which has Order ID, Order Date,
Customer Name, Total Sale, Payment Amount, Payment Date.

Not sure what you mean by SQL but I have copied this from the SQL view in
the query:

SELECT Orders.CustomerName, Sum(Orders.TotalSale) AS SumOfTotalSale,
Sum(Orders.PaymentAmount) AS SumOfPaymentAmount,
[SumOfTotalSale]-[SumOfPaymentAmount] AS [Outstanding Balance]
FROM Orders
GROUP BY Orders.CustomerName;

Regards
Jessy

This query has no criteria. Open it in the query grid and look at the Criteria
line. It should have something like what I suggested in the Criteria box under
Outstanding Balance.

Does that help?

I'll be leaving for a couple of weeks tomorrow morning so if you need more
help start a new thread.
 

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