How to make query show result if result is Null

  • Thread starter Thread starter Kind regards Donatas
  • Start date Start date
K

Kind regards Donatas

Hello, i am rather green to this stuff, so dont laugh if my question is funny.
I got numerous queries, and i need to have all results in 1 query, but some
of my result are Null and when i join up all queries in 1, i see only blank
space. For ex.: i have 2 querie field "120 (D).SumOfDebetas" and "121
(D).SumOfDebetas" one of them contains Null (no data at all on that querie),
but i need to "no data" as "0", how can i do that? Fast answer for greenie
would help ALOT. Thank you in Advance
 
You did not post the table and field names, so substitute them in this --
Some_Alias_Name: IIF([YourFieldName] Is Null, 0, [YourFieldName])
 
Hello Karl and thank you for your help. My table name is "Journal" field
names in it "Debetas" and "Kreditas". O tried to apply your example to my
queries, but i got more errors, like "Cannot group with "*"" Tries removing
grouping and summing from queries, but still got errors like "Having clause
Journal(all the names in criteria) without grouping or aggregation".


KARL DEWEY said:
You did not post the table and field names, so substitute them in this --
Some_Alias_Name: IIF([YourFieldName] Is Null, 0, [YourFieldName])
--
KARL DEWEY
Build a little - Test a little


Kind regards Donatas said:
Hello, i am rather green to this stuff, so dont laugh if my question is funny.
I got numerous queries, and i need to have all results in 1 query, but some
of my result are Null and when i join up all queries in 1, i see only blank
space. For ex.: i have 2 querie field "120 (D).SumOfDebetas" and "121
(D).SumOfDebetas" one of them contains Null (no data at all on that querie),
but i need to "no data" as "0", how can i do that? Fast answer for greenie
would help ALOT. Thank you in Advance
Click to expand...
Click to expand...
 
Post your query SQL. Open in design view, click on VIEW - SQL View,
highlight all, copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little


Kind regards Donatas said:
Hello Karl and thank you for your help. My table name is "Journal" field
names in it "Debetas" and "Kreditas". O tried to apply your example to my
queries, but i got more errors, like "Cannot group with "*"" Tries removing
grouping and summing from queries, but still got errors like "Having clause
Journal(all the names in criteria) without grouping or aggregation".


KARL DEWEY said:
You did not post the table and field names, so substitute them in this --
Some_Alias_Name: IIF([YourFieldName] Is Null, 0, [YourFieldName])
--
KARL DEWEY
Build a little - Test a little


Kind regards Donatas said:
Hello, i am rather green to this stuff, so dont laugh if my question is funny.
I got numerous queries, and i need to have all results in 1 query, but some
of my result are Null and when i join up all queries in 1, i see only blank
space. For ex.: i have 2 querie field "120 (D).SumOfDebetas" and "121
(D).SumOfDebetas" one of them contains Null (no data at all on that querie),
but i need to "no data" as "0", how can i do that? Fast answer for greenie
would help ALOT. Thank you in Advance
Click to expand...
Click to expand...
Click to expand...
 
SELECT Žurnalas.[Operacijos kodas (D)], Sum(Žurnalas.Debetas) AS SumOfDebetas
FROM Žurnalas
GROUP BY Žurnalas.[Operacijos kodas (D)]
HAVING (((Žurnalas.[Operacijos kodas (D)])=123));

thats the "many queries" thingie, all of them look alike, just some details
change, like field names etc.
And this is querie, that i have to see results in (even if i dont enter any
to Žurnalas table, i want to see it as 0)

SELECT [120 (D)].SumOfDebetas, [121 (D)].SumOfDebetas, [123
(D)].SumOfDebetas, [201 (D)].SumOfDebetas, [1237 (K)].SumOfKreditas, [27
(D)].SumOfDebetas, [27 (K)].SumOfKreditas, [201 (K)].SumOfKreditas, [301
(K)].SumOfKreditas, [443 (K)].SumOfKreditas, [443 (D)].SumOfDebetas, [Pelnas
ir nuostolis].SumOfDebetas, [Pelnas ir nuostolis].SumOfKreditas
FROM [120 (D)], [121 (D)], [123 (D)], [201 (D)], [1237 (K)], [27 (D)], [443
(D)], [27 (K)], [201 (K)], [301 (K)], [443 (K)], [Pelnas ir nuostolis];

A really great thanks to you Karl in advance. And thank you for your time
you wasted (on me) already.



KARL DEWEY said:
Post your query SQL. Open in design view, click on VIEW - SQL View,
highlight all, copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little


Kind regards Donatas said:
Hello Karl and thank you for your help. My table name is "Journal" field
names in it "Debetas" and "Kreditas". O tried to apply your example to my
queries, but i got more errors, like "Cannot group with "*"" Tries removing
grouping and summing from queries, but still got errors like "Having clause
Journal(all the names in criteria) without grouping or aggregation".


KARL DEWEY said:
You did not post the table and field names, so substitute them in this --
Some_Alias_Name: IIF([YourFieldName] Is Null, 0, [YourFieldName])
--
KARL DEWEY
Build a little - Test a little


:

Hello, i am rather green to this stuff, so dont laugh if my question is funny.
I got numerous queries, and i need to have all results in 1 query, but some
of my result are Null and when i join up all queries in 1, i see only blank
space. For ex.: i have 2 querie field "120 (D).SumOfDebetas" and "121
(D).SumOfDebetas" one of them contains Null (no data at all on that querie),
but i need to "no data" as "0", how can i do that? Fast answer for greenie
would help ALOT. Thank you in Advance
Click to expand...
Click to expand...
Click to expand...
 
1- >>i got more errors, like "Cannot group with "*""
I do not see an asteriak in your query.
2- You did not post the SQL of the query where you tried what I suggested.
3 - Your second query has 12 unrelated queries or tables. That will produce
records totaling all the records from each multiplied by each. That is known
as a Cartesian effect.
4- How many tables are you working with? What is the structure of your
tables? Post sample data. Explain a little more of what you are trying to do.
--
KARL DEWEY
Build a little - Test a little


Kind regards Donatas said:
SELECT Žurnalas.[Operacijos kodas (D)], Sum(Žurnalas.Debetas) AS SumOfDebetas
FROM Žurnalas
GROUP BY Žurnalas.[Operacijos kodas (D)]
HAVING (((Žurnalas.[Operacijos kodas (D)])=123));

thats the "many queries" thingie, all of them look alike, just some details
change, like field names etc.
And this is querie, that i have to see results in (even if i dont enter any
to Žurnalas table, i want to see it as 0)

SELECT [120 (D)].SumOfDebetas, [121 (D)].SumOfDebetas, [123
(D)].SumOfDebetas, [201 (D)].SumOfDebetas, [1237 (K)].SumOfKreditas, [27
(D)].SumOfDebetas, [27 (K)].SumOfKreditas, [201 (K)].SumOfKreditas, [301
(K)].SumOfKreditas, [443 (K)].SumOfKreditas, [443 (D)].SumOfDebetas, [Pelnas
ir nuostolis].SumOfDebetas, [Pelnas ir nuostolis].SumOfKreditas
FROM [120 (D)], [121 (D)], [123 (D)], [201 (D)], [1237 (K)], [27 (D)], [443
(D)], [27 (K)], [201 (K)], [301 (K)], [443 (K)], [Pelnas ir nuostolis];

A really great thanks to you Karl in advance. And thank you for your time
you wasted (on me) already.



KARL DEWEY said:
Post your query SQL. Open in design view, click on VIEW - SQL View,
highlight all, copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little


Kind regards Donatas said:
Hello Karl and thank you for your help. My table name is "Journal" field
names in it "Debetas" and "Kreditas". O tried to apply your example to my
queries, but i got more errors, like "Cannot group with "*"" Tries removing
grouping and summing from queries, but still got errors like "Having clause
Journal(all the names in criteria) without grouping or aggregation".


:

You did not post the table and field names, so substitute them in this --
Some_Alias_Name: IIF([YourFieldName] Is Null, 0, [YourFieldName])
--
KARL DEWEY
Build a little - Test a little


:

Hello, i am rather green to this stuff, so dont laugh if my question is funny.
I got numerous queries, and i need to have all results in 1 query, but some
of my result are Null and when i join up all queries in 1, i see only blank
space. For ex.: i have 2 querie field "120 (D).SumOfDebetas" and "121
(D).SumOfDebetas" one of them contains Null (no data at all on that querie),
but i need to "no data" as "0", how can i do that? Fast answer for greenie
would help ALOT. Thank you in Advance
Click to expand...
Click to expand...
Click to expand...
 
i have deleted your advise from query because it wouldnt start with it at
all. i am working with 1 table Žurnalas. I get records in there, and i need
to extract data from it by code row (balance). 12 unrelated queries because
every one of them, gets data for me i need. And i need to combine all of
those queries to one query, where i can see all of them at one time, but my
problem is, that i cant see them, because some of them have Null value(i dont
enter data to the table, so query doesnt have anything to show me), and i
need to replace that Null value with 0, so i dont see the blank query.
"IIF([YourFieldName] Is Null, 0, [YourFieldName]) " in YouFieldName i should
enter queries field name, or table field name? If tables, then could tell me
where do i make mistake?
SELECT Žurnalas.[Operacijos kodas (D)], Sum(Žurnalas.Debetas) AS SumOfDebetas
FROM Žurnalas
GROUP BY Žurnalas.[Operacijos kodas (D)]
HAVING (((Žurnalas.[Operacijos kodas (D)])=121) AND
((Sum(Žurnalas.Debetas))=IIf([Žurnalas]![Debetas] Is Null,0,[SumOfDebetas])));
i get error like: You tried to execute the query that doesnt include the
specific expresion.

i am getting data from row that match's criteria of field
Žurnalas.[Operacijos kodas (D)]

KARL DEWEY said:
1- >>i got more errors, like "Cannot group with "*""
I do not see an asteriak in your query.
2- You did not post the SQL of the query where you tried what I suggested.
3 - Your second query has 12 unrelated queries or tables. That will produce
records totaling all the records from each multiplied by each. That is known
as a Cartesian effect.
4- How many tables are you working with? What is the structure of your
tables? Post sample data. Explain a little more of what you are trying to do.
--
KARL DEWEY
Build a little - Test a little


Kind regards Donatas said:
SELECT Žurnalas.[Operacijos kodas (D)], Sum(Žurnalas.Debetas) AS SumOfDebetas
FROM Žurnalas
GROUP BY Žurnalas.[Operacijos kodas (D)]
HAVING (((Žurnalas.[Operacijos kodas (D)])=123));

thats the "many queries" thingie, all of them look alike, just some details
change, like field names etc.
And this is querie, that i have to see results in (even if i dont enter any
to Žurnalas table, i want to see it as 0)

SELECT [120 (D)].SumOfDebetas, [121 (D)].SumOfDebetas, [123
(D)].SumOfDebetas, [201 (D)].SumOfDebetas, [1237 (K)].SumOfKreditas, [27
(D)].SumOfDebetas, [27 (K)].SumOfKreditas, [201 (K)].SumOfKreditas, [301
(K)].SumOfKreditas, [443 (K)].SumOfKreditas, [443 (D)].SumOfDebetas, [Pelnas
ir nuostolis].SumOfDebetas, [Pelnas ir nuostolis].SumOfKreditas
FROM [120 (D)], [121 (D)], [123 (D)], [201 (D)], [1237 (K)], [27 (D)], [443
(D)], [27 (K)], [201 (K)], [301 (K)], [443 (K)], [Pelnas ir nuostolis];

A really great thanks to you Karl in advance. And thank you for your time
you wasted (on me) already.



KARL DEWEY said:
Post your query SQL. Open in design view, click on VIEW - SQL View,
highlight all, copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little


:

Hello Karl and thank you for your help. My table name is "Journal" field
names in it "Debetas" and "Kreditas". O tried to apply your example to my
queries, but i got more errors, like "Cannot group with "*"" Tries removing
grouping and summing from queries, but still got errors like "Having clause
Journal(all the names in criteria) without grouping or aggregation".


:

You did not post the table and field names, so substitute them in this --
Some_Alias_Name: IIF([YourFieldName] Is Null, 0, [YourFieldName])
--
KARL DEWEY
Build a little - Test a little


:

Hello, i am rather green to this stuff, so dont laugh if my question is funny.
I got numerous queries, and i need to have all results in 1 query, but some
of my result are Null and when i join up all queries in 1, i see only blank
space. For ex.: i have 2 querie field "120 (D).SumOfDebetas" and "121
(D).SumOfDebetas" one of them contains Null (no data at all on that querie),
but i need to "no data" as "0", how can i do that? Fast answer for greenie
would help ALOT. Thank you in Advance
Click to expand...
Click to expand...
Click to expand...
 
Try this --
SELECT Žurnalas.[Operacijos kodas (D)], Sum(IIF([Žurnalas].[Debetas] Is
Null, 0, [Žurnalas].[Debetas]) AS SumOfDebetas
FROM Žurnalas
GROUP BY Žurnalas.[Operacijos kodas (D)]
HAVING ((Žurnalas.[Operacijos kodas (D)])=121)]);

I think your information could be pulled using only one query.
Post the table structure of Žurnalas and sample data.
--
KARL DEWEY
Build a little - Test a little


Kind regards Donatas said:
i have deleted your advise from query because it wouldnt start with it at
all. i am working with 1 table Žurnalas. I get records in there, and i need
to extract data from it by code row (balance). 12 unrelated queries because
every one of them, gets data for me i need. And i need to combine all of
those queries to one query, where i can see all of them at one time, but my
problem is, that i cant see them, because some of them have Null value(i dont
enter data to the table, so query doesnt have anything to show me), and i
need to replace that Null value with 0, so i dont see the blank query.
"IIF([YourFieldName] Is Null, 0, [YourFieldName]) " in YouFieldName i should
enter queries field name, or table field name? If tables, then could tell me
where do i make mistake?
SELECT Žurnalas.[Operacijos kodas (D)], Sum(Žurnalas.Debetas) AS SumOfDebetas
FROM Žurnalas
GROUP BY Žurnalas.[Operacijos kodas (D)]
HAVING (((Žurnalas.[Operacijos kodas (D)])=121) AND
((Sum(Žurnalas.Debetas))=IIf([Žurnalas]![Debetas] Is Null,0,[SumOfDebetas])));
i get error like: You tried to execute the query that doesnt include the
specific expresion.

i am getting data from row that match's criteria of field
Žurnalas.[Operacijos kodas (D)]

KARL DEWEY said:
1- >>i got more errors, like "Cannot group with "*""
I do not see an asteriak in your query.
2- You did not post the SQL of the query where you tried what I suggested.
3 - Your second query has 12 unrelated queries or tables. That will produce
records totaling all the records from each multiplied by each. That is known
as a Cartesian effect.
4- How many tables are you working with? What is the structure of your
tables? Post sample data. Explain a little more of what you are trying to do.
--
KARL DEWEY
Build a little - Test a little


Kind regards Donatas said:
SELECT Žurnalas.[Operacijos kodas (D)], Sum(Žurnalas.Debetas) AS SumOfDebetas
FROM Žurnalas
GROUP BY Žurnalas.[Operacijos kodas (D)]
HAVING (((Žurnalas.[Operacijos kodas (D)])=123));

thats the "many queries" thingie, all of them look alike, just some details
change, like field names etc.
And this is querie, that i have to see results in (even if i dont enter any
to Žurnalas table, i want to see it as 0)

SELECT [120 (D)].SumOfDebetas, [121 (D)].SumOfDebetas, [123
(D)].SumOfDebetas, [201 (D)].SumOfDebetas, [1237 (K)].SumOfKreditas, [27
(D)].SumOfDebetas, [27 (K)].SumOfKreditas, [201 (K)].SumOfKreditas, [301
(K)].SumOfKreditas, [443 (K)].SumOfKreditas, [443 (D)].SumOfDebetas, [Pelnas
ir nuostolis].SumOfDebetas, [Pelnas ir nuostolis].SumOfKreditas
FROM [120 (D)], [121 (D)], [123 (D)], [201 (D)], [1237 (K)], [27 (D)], [443
(D)], [27 (K)], [201 (K)], [301 (K)], [443 (K)], [Pelnas ir nuostolis];

A really great thanks to you Karl in advance. And thank you for your time
you wasted (on me) already.



:

Post your query SQL. Open in design view, click on VIEW - SQL View,
highlight all, copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little


:

Hello Karl and thank you for your help. My table name is "Journal" field
names in it "Debetas" and "Kreditas". O tried to apply your example to my
queries, but i got more errors, like "Cannot group with "*"" Tries removing
grouping and summing from queries, but still got errors like "Having clause
Journal(all the names in criteria) without grouping or aggregation".


:

You did not post the table and field names, so substitute them in this --
Some_Alias_Name: IIF([YourFieldName] Is Null, 0, [YourFieldName])
--
KARL DEWEY
Build a little - Test a little


:

Hello, i am rather green to this stuff, so dont laugh if my question is funny.
I got numerous queries, and i need to have all results in 1 query, but some
of my result are Null and when i join up all queries in 1, i see only blank
space. For ex.: i have 2 querie field "120 (D).SumOfDebetas" and "121
(D).SumOfDebetas" one of them contains Null (no data at all on that querie),
but i need to "no data" as "0", how can i do that? Fast answer for greenie
would help ALOT. Thank you in Advance
Click to expand...
Click to expand...
Click to expand...
 
Syntax error (missing operator) in query expresion, after hitting "ok" it
jumps on "AS" operator.
By the way, thank you for sugestion about query, but i need all of them, as
part of my task.

KARL DEWEY said:
Try this --
SELECT Žurnalas.[Operacijos kodas (D)], Sum(IIF([Žurnalas].[Debetas] Is
Null, 0, [Žurnalas].[Debetas]) AS SumOfDebetas
FROM Žurnalas
GROUP BY Žurnalas.[Operacijos kodas (D)]
HAVING ((Žurnalas.[Operacijos kodas (D)])=121)]);

I think your information could be pulled using only one query.
Post the table structure of Žurnalas and sample data.
--
KARL DEWEY
Build a little - Test a little


Kind regards Donatas said:
i have deleted your advise from query because it wouldnt start with it at
all. i am working with 1 table Žurnalas. I get records in there, and i need
to extract data from it by code row (balance). 12 unrelated queries because
every one of them, gets data for me i need. And i need to combine all of
those queries to one query, where i can see all of them at one time, but my
problem is, that i cant see them, because some of them have Null value(i dont
enter data to the table, so query doesnt have anything to show me), and i
need to replace that Null value with 0, so i dont see the blank query.
"IIF([YourFieldName] Is Null, 0, [YourFieldName]) " in YouFieldName i should
enter queries field name, or table field name? If tables, then could tell me
where do i make mistake?
SELECT Žurnalas.[Operacijos kodas (D)], Sum(Žurnalas.Debetas) AS SumOfDebetas
FROM Žurnalas
GROUP BY Žurnalas.[Operacijos kodas (D)]
HAVING (((Žurnalas.[Operacijos kodas (D)])=121) AND
((Sum(Žurnalas.Debetas))=IIf([Žurnalas]![Debetas] Is Null,0,[SumOfDebetas])));
i get error like: You tried to execute the query that doesnt include the
specific expresion.

i am getting data from row that match's criteria of field
Žurnalas.[Operacijos kodas (D)]

KARL DEWEY said:
1- >>i got more errors, like "Cannot group with "*""
I do not see an asteriak in your query.
2- You did not post the SQL of the query where you tried what I suggested.
3 - Your second query has 12 unrelated queries or tables. That will produce
records totaling all the records from each multiplied by each. That is known
as a Cartesian effect.
4- How many tables are you working with? What is the structure of your
tables? Post sample data. Explain a little more of what you are trying to do.
--
KARL DEWEY
Build a little - Test a little


:

SELECT Žurnalas.[Operacijos kodas (D)], Sum(Žurnalas.Debetas) AS SumOfDebetas
FROM Žurnalas
GROUP BY Žurnalas.[Operacijos kodas (D)]
HAVING (((Žurnalas.[Operacijos kodas (D)])=123));

thats the "many queries" thingie, all of them look alike, just some details
change, like field names etc.
And this is querie, that i have to see results in (even if i dont enter any
to Žurnalas table, i want to see it as 0)

SELECT [120 (D)].SumOfDebetas, [121 (D)].SumOfDebetas, [123
(D)].SumOfDebetas, [201 (D)].SumOfDebetas, [1237 (K)].SumOfKreditas, [27
(D)].SumOfDebetas, [27 (K)].SumOfKreditas, [201 (K)].SumOfKreditas, [301
(K)].SumOfKreditas, [443 (K)].SumOfKreditas, [443 (D)].SumOfDebetas, [Pelnas
ir nuostolis].SumOfDebetas, [Pelnas ir nuostolis].SumOfKreditas
FROM [120 (D)], [121 (D)], [123 (D)], [201 (D)], [1237 (K)], [27 (D)], [443
(D)], [27 (K)], [201 (K)], [301 (K)], [443 (K)], [Pelnas ir nuostolis];

A really great thanks to you Karl in advance. And thank you for your time
you wasted (on me) already.



:

Post your query SQL. Open in design view, click on VIEW - SQL View,
highlight all, copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little


:

Hello Karl and thank you for your help. My table name is "Journal" field
names in it "Debetas" and "Kreditas". O tried to apply your example to my
queries, but i got more errors, like "Cannot group with "*"" Tries removing
grouping and summing from queries, but still got errors like "Having clause
Journal(all the names in criteria) without grouping or aggregation".


:

You did not post the table and field names, so substitute them in this --
Some_Alias_Name: IIF([YourFieldName] Is Null, 0, [YourFieldName])
--
KARL DEWEY
Build a little - Test a little


:

Hello, i am rather green to this stuff, so dont laugh if my question is funny.
I got numerous queries, and i need to have all results in 1 query, but some
of my result are Null and when i join up all queries in 1, i see only blank
space. For ex.: i have 2 querie field "120 (D).SumOfDebetas" and "121
(D).SumOfDebetas" one of them contains Null (no data at all on that querie),
but i need to "no data" as "0", how can i do that? Fast answer for greenie
would help ALOT. Thank you in Advance
Click to expand...
Click to expand...
Click to expand...
 
Missing closing parenthesis --
SELECT Žurnalas.[Operacijos kodas (D)], Sum(IIF([Žurnalas].[Debetas] Is
Null, 0, [Žurnalas].[Debetas])) AS SumOfDebetas
FROM Žurnalas
GROUP BY Žurnalas.[Operacijos kodas (D)]
HAVING ((Žurnalas.[Operacijos kodas (D)])=121)]);

I think ALL of your information could be pulled using only one query.
Post the table structure of Žurnalas and sample data.
 
it still doesnt work for me...well my table info is (by columns): "ID"
"Operacijos data" "Operacija" "Operacijos kodas (D)" "Debetas" "Operacijos
kodas (K)" "Kreditas" and i need to extract data, by operation code
"Operacijos kodas (D)" and "Operacijos kodas (K)" resuls i need to see is sum
of Debetas and sum of Kreditas.

by the way, something is wrong with the end of this line HAVING
((Žurnalas.[Operacijos kodas (D)])=121)]); (too many closing "]" and ")" ?
 
Back
Top