Adding "0"'s to records when blanks...

[Guest]

Dear helpers,

I have two tables which I use to create a query. An example:

Tbl1: Tbl2:

IDN Name Batch Batch Faults Produced
---- ------- ------ -------- ------ ----------
1 Joe A12 A12 3 200
2 Joe A13 A13 1 100
3 Black A14 A15 2 250
4 John A15
5 Joe A16

The query1 will be Tbl1 --------> Tbl2 (One to many relationship). The
outcome is what I do not want:

Qery1:

IDN Name Batch Produced
1 Joe A12 200
2 Joe A13 100
3 Black A14
4 John A15 250
5 Joe A16

Qery1.Produced at IDN 3 and 5 are "blanks". But I want that there is a ZERO
= "0";

Qery1:

IDN Name Batch Produced
1 Joe A12 200
2 Joe A13 100
3 Black A14 0
4 John A15 250
5 Joe A16 0


I devine that it is a very common problem, but I am just very new with
Access and SQL.

ps.... When about SQL. I am from Slovakia and some people pronounce SQL like

[es - kju - el] like S. Q. L.

but I have read somewhere that "experts" do pronounce [sikquel] like SEE
QUEL. What is "experts-like" correct?

 

[Colin]

Access help provides the solution as follows:

Converting Null values to zero
When you have fields that contain Null values, you can
create an expression 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
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)

If you use the answer wizard in help and type Return Zero
instead of null select about working with blank fields

Regards Colin

 

[SJ McAbney]

Create a calculated field within your query and use the Nz
() function.

i.e.

SELECT MyTableID, MyCustomer, Nz(MyField, 0) AS NewField
FROM MyTable
ORDER BY MyCustomer;

 

[Michel Walsh]

HI,


with Jet+Acxess:

SELECT tbl1.IDN, tbl1.Name, tbl1.Batch, NZ( tbl2.Produced, 0)
FROM tbl1 LEFT JOIN tbl2 ON tbl1.Batch=tbl2.Batch



with Jet, outside Access

SELECT tbl1.IDN, tbl1.Name, tbl1.Batch, iif( tbl2.Produced IS NULL, 0,
tbl2.Produced )
FROM tbl1 LEFT JOIN tbl2 ON tbl1.Batch=tbl2.Batch


with MS SQL Server

SELECT tbl1.IDN, tbl1.Name, tbl1.Batch, COALESCE( tbl2.Produced, 0)
FROM tbl1 LEFT JOIN tbl2 ON tbl1.Batch=tbl2.Batch




Both pronunciations are acceptable, so an SQL statement ( an es-kju- el )
or a SQL statement ( a sea-kuel). English native speaker having learn it
at its beginning have a tendency to say sea-kuel, which was the original
pronunciation, but the letter by letter pronunciation has been official and
accepted too (and it sounds a little bit pejorative, at least in French, to
say sea-kuel, because its pronunciation is close to a word meaning
"disease after effects", so, in French, almost every one pronounces
es-ku-el., and not as if it was a disease consequence)



Hoping it may help,
Vanderghast, Access MVP

 

[Gary Walter]

:

or a SQL statement ( a sea-kuel). English native speaker having learn it
at its beginning have a tendency to say sea-kuel, which was the original
pronunciation, but the letter by letter pronunciation has been official and
accepted too (and it sounds a little bit pejorative, at least in French, to
say sea-kuel, because its pronunciation is close to a word meaning
"disease after effects", so, in French, almost every one pronounces
es-ku-el., and not as if it was a disease consequence)



Hoping it may help,
Vanderghast, Access MVP

Sometimes it feels like a French sea-kuel

gary

 

[Michel Walsh]

Hi,



... it is normal if it is not normal-ized...



Vanderghast, Access MVP

 

[Guest]

Richard, I came across your question looking for a similar solution, see if
this page answer your problem. I didn't resolve mine but it might help with
yours!

Good luck.

P.S. I am in Miami, Florida USA, we prononuce it [see quel]

http://msdn.microsoft.com/library/d...ngwithbankfieldszerolengthstringsqueriess.asp

 

[Guest]

Thanks for the great advice in this thread... I used the NZ solution for my
following code which changed a null condition (where a customer may not have
a transaction existing in the transactions table) to zero so that at report
time, the query just hands off a transaction sum of zero (and the report
doesn't print a blank) for that customer.... Works great !

Scott in Texas

SELECT DISTINCTROW Customers.CustomerID, Customers.[Customer First Name],
Customers.[Customer Last Name], Customers.[Customer Phone Number],
Customers.[Customer Acct Balance], Sum(NZ(Transactions.TransactionAmount,0))
AS [Sum Of TransactionAmount]
FROM Customers LEFT JOIN Transactions ON
Customers.CustomerID=Transactions.CustomerID
GROUP BY Customers.CustomerID, Customers.[Customer First Name],
Customers.[Customer Last Name], Customers.[Customer Phone Number],
Customers.[Customer Acct Balance];